from django.core.paginator import Paginator
from django.shortcuts import render
from rest_framework.decorators import api_view
from django.shortcuts import render
from rest_framework import generics
from django.db.models import Count, Q
from django.http import HttpResponse
import datetime
import time
import openpyxl
import os
import requests
from django.http import StreamingHttpResponse
from rest_framework.decorators import api_view, permission_classes
from thdata.sql import *
from django.http import HttpResponse
from thdata.func import *
import json
from method.redis import get_redis_client
from collections import OrderedDict
from .models import *
from method.tools import DateProcess
from dateutil.relativedelta import relativedelta


def response(data: object) -> object:
    re = Response(data)
    re['Access-Control-Allow-Origin'] = '*'
    re["Access-Control-Allow-Credentials"] = "true"
    re['Content-Type'] = 'application/json'
    return re


@api_view(http_method_names=["GET"])
def ps_nav(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            site = request.GET.get('site', '')
            building = request.GET.get('building', '')
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            parm_list = ['site', 'building', 'floor', 'sections', 'gw_name']
            sql = "SELECT DISTINCT {} from (SELECT equipment_id FROM `sanse_deng_list` WHERE sensors_type='air_sensor') a " \
                  "LEFT JOIN st126_device b ON a.equipment_id=b.gw_id WHERE customer = 'ST'{}"
            data_len = parm_list[len(request.GET)]
            if site == "":
                sql = sql.format(data_len, '')
            if site and building == '' and floor == '':
                f_sql = "and site='{}'".format(site)
                sql = sql.format(data_len, f_sql)
            if site and building and floor == '':
                f_sql = "and site='{}' and building='{}' ".format(site, building)
                sql = sql.format(data_len, f_sql)
            if floor and sections == '':
                f_sql = "and site='{}' and building='{}' and floor='{}' ".format(site, building, floor)
                sql = sql.format(data_len, f_sql)
            if sections:
                f_sql = "and site='{}' and building='{}' and floor='{}'and sections='{}' " \
                    .format(site, building, floor, sections)
                sql = sql.format(data_len, f_sql)
            data = my_select1(sql)
            data = [i[0] for i in data]
            content['payload'] = data
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def pressure(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            date_list = []
            trend_data = []
            time_to = datetime.datetime.now()
            in_time = time_to.strftime("%Y-%m-%d %H:%M")
            gw_name = request.GET.get('gw_name', '')
            on_time = request.GET.get('on_time', '')
            if not gw_name: raise Exception(u"請傳入監測點")
            gw_sql = "SELECT equipment_id FROM `sanse_deng_list` \
                     WHERE sensors_type='air_sensor' AND equipment_name='{}'"
            gw_data = my_select1(gw_sql.format(gw_name))
            ps_data = 0
            if time_to.minute <= 30:
                state_time = time_to.strftime("%Y-%m-%d %H:00")
            else:
                state_time = time_to.strftime("%Y-%m-%d %H:29")
            re_data = red_conn("st:ps:" + gw_data[0][0])
            if re_data:
                ps_date = list(eval(re_data).keys())
                if re_data and (eval(re_data))[ps_date[0]] != 'Error' and ps_date[0] > state_time:
                    ps_data = round(int(list(eval(re_data).values())[0]) / 1000, 2)
            one_data = my_select1(SQL["PRESSURE_ONE"].format(gw_name, state_time))
            count_data = my_select1(SQL["PRESSURE_COUNT"].format(in_time, gw_name))
            if on_time and int(time_to.day) != int(on_time[8:10]):
                date_list = []
                for i in range(24):
                    date_list.append('{num:02d}'.format(num=i) + ":00")
                    date_list.append('{num:02d}'.format(num=i) + ":30")
                count_data = my_select1(SQL["PRESSURE_COUNT"].format(on_time, gw_name))
            else:
                for i in range(time_to.hour + 1):
                    date_list.append('{num:02d}'.format(num=i) + ":00")
                    date_list.append('{num:02d}'.format(num=i) + ":30")
                if time_to.minute < 30:
                    date_list = date_list[0:-1]
            for i in date_list:
                if i in dict(count_data):
                    trend_data.append(dict(count_data)[i])
                else:
                    trend_data.append("")
            content['payload'] = {"trend_data": trend_data, "trend_date": date_list, "one_data": ps_data}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def th_nav(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            site = request.GET.get('site', '')
            building = request.GET.get('building', '')
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            parm_list = ['site', 'building', 'floor', 'sections', 'gw_name']
            # sql = "SELECT DISTINCT {} FROM `sanse_deng_list` a LEFT JOIN st126_device b ON a.equipment_id=b.gw_id  \
            #       where  sensors_type='temp_hum_sensor' {}"
            sql = "SELECT DISTINCT {0} FROM `st126_device` WHERE customer='ST' and {0}!='NA' AND enable_flag=1 {1}"
            data_len = parm_list[len(request.GET)]
            if site == "":
                sql = sql.format(data_len, '')
            if site and building == '' and floor == '':
                f_sql = "and site='{}'".format(site)
                sql = sql.format(data_len, f_sql)
            if site and building and floor == '':
                f_sql = "and site='{}' and building='{}' ".format(site, building)
                sql = sql.format(data_len, f_sql)
            if floor and sections == '':
                f_sql = "and site='{}' and building='{}' and floor='{}' ".format(site, building, floor)
                sql = sql.format(data_len, f_sql)
            if sections:
                f_sql = "and site='{}' and building='{}' and floor='{}'and sections='{}' " \
                    .format(site, building, floor, sections)
                sql = sql.format(data_len, f_sql)
            data = my_select1(sql)
            data = [i[0] for i in data]
            content['payload'] = data
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def th_state(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            gw_name = request.GET.get('gw_name', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            timeto = datetime.datetime.now().strftime("%Y-%m-%d")
            timeto2 = datetime.datetime.now()
            yesterday = timeto2 + datetime.timedelta(days=-1)
            if not floor and not gw_name:
                cat_sql = ''
                sele_name = "sections"
            elif floor and not sections:
                cat_sql = "and floor = '%s'" % (floor)
                sele_name = "sections"
            elif floor and sections and not gw_name:
                cat_sql = "and floor = '%s' and sections = '%s'" % (floor, sections)
                sele_name = "sections"
            elif floor and sections and gw_name:
                cat_sql = "and floor = '%s' and sections = '%s' and gw_name= '%s'" % (floor, sections, gw_name)
                sele_name = "gw_name"
            # th_state_data = my_select1(SQL['TOTAL_TH_STATE'].format(sele_name, cat_sql, sele_name))

            if not end_time and not start_time:
                flag = "PD"
                th_trend_data = my_select1(SQL['TOTAL_TH_TREND'].format(sele_name, cat_sql, timeto, sele_name))
            else:
                if end_time == start_time:
                    flag = "PD"
                    th_trend_data = my_select1(SQL['TOTAL_TH_TREND'].format(sele_name, cat_sql, start_time, sele_name))
                else:
                    flag = "DAY"
                    th_trend_data = my_select1(SQL['TOTAL_TH_TREND_DAY']
                                               .format(sele_name, start_time, end_time, cat_sql, sele_name))
            th_trend_data2 = [[i[0], i[1], i[2], i[3]] for i in th_trend_data]
            th_dict = {str(i[0]) + str(i[1]): [i[2], i[3]] for i in th_trend_data2}
            ses = []
            date_list = []
            cz_sec = {}
            for i in th_trend_data2:
                if i[1] not in ses:
                    ses.append(i[1])
            for i in th_trend_data2:
                if i[0] not in date_list:
                    date_list.append(i[0])
            for i in ses:
                date_dict = {}
                for j in date_list:
                    if j + i in th_dict:
                        date_dict[j] = th_dict[j + i]
                    else:
                        date_dict[j] = [0, 0]
                cz_sec[i] = date_dict
            # th_state_data2 = {i[0]: [i[1], i[2]] for i in th_state_data}
            content['payload'] = {"th_trend": cz_sec, "flag": flag}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def th_se_state(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            sections = request.GET.get('sections', '')
            gw_name = request.GET.get('gw_name', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            timeto = datetime.datetime.now().strftime("%Y-%m-%d")
            timeto2 = datetime.datetime.now()
            yesterday = timeto2 + datetime.timedelta(days=-1)
            th_sql = "SELECT DISTINCT {} FROM `sanse_deng_list` a LEFT JOIN st126_device b ON a.equipment_id = b.gw_id \
                    WHERE sensors_type = 'temp_hum_sensor' {} "
            gw_sql = "SELECT gw_id FROM `sanse_deng_list` a LEFT JOIN st126_device b ON a.equipment_id = b.gw_id \
                     WHERE sensors_type = 'temp_hum_sensor' and {}= '{}'"
            if not sections and not gw_name:
                cat_sql = ''
                sele_name = "sections"
            elif sections and not gw_name:
                cat_sql = "and sections = '%s'" % (sections)
                sele_name = "sections"
            elif sections and gw_name:
                cat_sql = "and sections = '%s' and gw_name= '%s'" % (sections, gw_name)
                sele_name = "gw_name"
            th_data = my_select1(th_sql.format(sele_name, cat_sql, sele_name))
            th_count = {}
            if th_data:
                for i in th_data:
                    gw_data = my_select1(gw_sql.format(sele_name, i[0]))
                    tem_avg = []
                    hum_avg = []
                    if gw_data:
                        for j in gw_data:
                            re_data = red_conn("st:th:" + j[0])
                            if re_data:
                                ps_date = eval(re_data)
                                tem_avg.append(float(ps_date['tem']))
                                hum_avg.append(float(ps_date['hum']))
                        if tem_avg:
                            th_count[i[0]] = [round(sum(tem_avg) / len(tem_avg), 1),
                                              round(sum(hum_avg) / len(hum_avg), 1)]
            content['payload'] = th_count
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def tem_state_day(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            sections = request.GET.get('sections', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            if not start_time and not end_time:
                end_time = datetime.date.today()
                start_time = end_time - relativedelta(days=6)

            ses_data = my_select1(SQL['SES_TH_STATE'].
                                  format("sections", start_time, end_time, sections, "sections"))
            area_data = my_select1(SQL['SES_TH_STATE'].
                                   format("gw_name", start_time, end_time, sections, "gw_name"))
            th_trend_data2 = [[i[0], i[1], i[3], i[4], i[2]] for i in area_data]
            th_dict = {str(i[0]) + str(i[1]): [i[2], i[3], i[4]] for i in th_trend_data2}
            ses = []
            date_list = []
            cz_sec = {}
            for i in th_trend_data2:
                if i[1] not in ses:
                    ses.append(i[1])
            for i in th_trend_data2:
                if i[0] not in date_list:
                    date_list.append(i[0])
            for i in ses:
                date_dict = {}
                for j in date_list:
                    if j + i in th_dict:
                        date_dict[j] = th_dict[j + i]
                    else:
                        date_dict[j] = [0, 0, 0]
                cz_sec[i] = date_dict
            avg_tem = [i[2] for i in list(ses_data)]
            min_tem = [i[4] for i in list(ses_data)]
            max_tem = [i[3] for i in list(ses_data)]
            th_date = [i[0] for i in list(ses_data)]
            content['payload'] = {"st": {"date": th_date, "avg_tem": avg_tem, "min_tem": min_tem, "max_tem": max_tem},
                                  "st2": cz_sec}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def hum_state_day(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            sections = request.GET.get('sections', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            if not start_time and not end_time:
                end_time = datetime.date.today()
                start_time = end_time - relativedelta(days=6)
            ses_data = my_select1(SQL['SES_TH_STATE'].
                                  format("sections", start_time, end_time, sections, "sections"))
            area_data = my_select1(SQL['SES_TH_STATE'].
                                   format("gw_name", start_time, end_time, sections, "gw_name"))
            th_trend_data2 = [[i[0], i[1], i[6], i[7], i[5]] for i in area_data]
            th_dict = {str(i[0]) + str(i[1]): [i[2], i[3], i[4]] for i in th_trend_data2}
            ses = []
            date_list = []
            cz_sec = {}
            for i in th_trend_data2:
                if i[1] not in ses:
                    ses.append(i[1])
            for i in th_trend_data2:
                if i[0] not in date_list:
                    date_list.append(i[0])
            for i in ses:
                date_dict = {}
                for j in date_list:
                    if j + i in th_dict:
                        date_dict[j] = th_dict[j + i]
                    else:
                        date_dict[j] = [0, 0, 0]
                cz_sec[i] = date_dict
            avg_hum = [i[5] for i in list(ses_data)]
            min_hum = [i[7] for i in list(ses_data)]
            max_hum = [i[6] for i in list(ses_data)]
            th_date = [i[0] for i in list(ses_data)]
            content['payload'] = {"st": {"date": th_date, "avg_hum": avg_hum, "min_hum": min_hum, "max_hum": max_hum},
                                  "st2": cz_sec}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def th_total(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            tot_sql = "SELECT sections,count(gw_id) FROM (SELECT equipment_id \
                        FROM `sanse_deng_list` WHERE sensors_type = 'temp_hum_sensor' ) a \
                        LEFT JOIN st126_device b ON a.equipment_id = b.gw_id GROUP BY sections"
            machine_data = my_select1(tot_sql)
            content['payload'] = dict(machine_data)
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def th_defect_top(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            by_time = request.GET.get('by_time', '')
            timeto = datetime.date.today()
            if not by_time: raise Exception(u"請傳入需要查看的範圍")
            hum_sql = "SELECT sections,count(a.gw_id) FROM `tb_iot_defect` a LEFT JOIN st126_device b ON \
                       a.gw_id=b.gw_id WHERE defect_type='hum' AND total_time>300  AND DATE(end_time)>='{}'\
                       AND DATE(end_time)<='{}' GROUP BY sections ORDER BY RIGHT(sections,4)"
            tem_sql = "SELECT sections,count(a.gw_id) FROM `tb_iot_defect` a LEFT JOIN st126_device b ON \
                       a.gw_id=b.gw_id WHERE defect_type='tem' AND total_time>300  AND DATE(end_time)>='{}'\
                       AND DATE(end_time)<='{}' GROUP BY sections ORDER BY RIGHT(sections,4)"
            if by_time == 'day':
                str_time = timeto
            elif by_time == 'seven_day':
                str_time = timeto - relativedelta(days=6)
            elif by_time == 'month':
                str_time = timeto - relativedelta(months=1)
            elif by_time == 'three_month':
                str_time = timeto - relativedelta(months=2)
            elif by_time == 'six_month':
                str_time = timeto - relativedelta(months=5)
            else:
                str_time = timeto
            tem_top = my_select1(tem_sql.format(str_time, timeto))
            hum_top = my_select1(hum_sql.format(str_time, timeto))
            content['payload'] = {'tem_top': tem_top, 'hum_top': hum_top}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def th_detail(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            sections = request.GET.get('sections', '')
            if not sections: raise Exception(u"請傳入車間名稱")
            pre_detail_sql = "SELECT gw_id,sections,sanse_deng,gw_name,ip_address FROM `sanse_deng_list` a LEFT JOIN  \
                            st126_device b ON a.equipment_id=b.gw_id where sensors_type='temp_hum_sensor' AND sections='{}'"
            gw_data = my_select1(pre_detail_sql.format(sections))
            pre_detail = []
            if gw_data:
                for i in gw_data:
                    ma_detail = {}
                    ma_detail['sections'] = i[1]
                    ma_detail['iot'] = i[2]
                    ma_detail['gw_name'] = i[3]
                    ma_detail['ip'] = i[4]
                    ma_detail['tem'] = 'NA'
                    ma_detail['hum'] = 'NA'
                    ma_detail['flag'] = 0
                    re_data = red_conn("st:th:" + i[0])
                    if re_data:
                        ps_date = eval(re_data)
                        ma_detail['tem'] = ps_date['tem']
                        ma_detail['hum'] = ps_date['hum']
                        if float(ma_detail['tem']) > 19 and float(ma_detail['tem']) < 25 and float(
                                ma_detail['hum']) < 65:
                            ma_detail['flag'] = 1
                    pre_detail.append(ma_detail)
            content['payload'] = pre_detail
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def th_defect_detail(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            timeto = datetime.date.today()
            sections = request.GET.get('sections', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            if not sections: raise Exception(u"請傳入車間名稱")
            tem_sql = "SELECT sections,gw_name,start_time,end_time,total_time,flag FROM `tb_iot_defect` a\
                    LEFT JOIN st126_device b ON a.gw_id = b.gw_id WHERE sections='{}' AND defect_type='tem'\
                    AND total_time>300 AND DATE(end_time)>='{}' AND DATE(end_time)<='{}'"
            hum_sql = "SELECT sections,gw_name,start_time,end_time,total_time,flag FROM `tb_iot_defect` a\
                    LEFT JOIN st126_device b ON a.gw_id = b.gw_id WHERE sections='{}' AND defect_type='hum'\
                     AND total_time>300 AND  DATE(end_time)>='{}' AND DATE(end_time)<='{}'"
            if not start_time and not end_time:
                end_time = timeto
                start_time = timeto - relativedelta(days=6)
            tem_defect_detail = my_select1(tem_sql.format(sections, start_time, end_time))
            hum_defect_detail = my_select1(hum_sql.format(sections, start_time, end_time))
            tem_data = []
            hum_data = []
            for row in tem_defect_detail:
                data_row = list(row)
                data_row[2] = data_row[2].strftime("%Y-%m-%d %H:%M:%S")
                data_row[3] = data_row[3].strftime("%Y-%m-%d %H:%M:%S")
                tem_data.append(data_row)
            for row in hum_defect_detail:
                data_row = list(row)
                data_row[2] = data_row[2].strftime("%Y-%m-%d %H:%M:%S")
                data_row[3] = data_row[3].strftime("%Y-%m-%d %H:%M:%S")
                hum_data.append(data_row)
            content['payload'] = {'tem_defect_detail': tem_data, 'hum_defect_detail': hum_data}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def format_gantt(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            start = request.GET.get('start', None)
            end = request.GET.get('end', None)
            gw_ids = Sen.objects.values_list('equipment_id', flat=True).filter(sensors_type="sensor").distinct()
            start = datetime.datetime.strptime(start, '%Y-%m-%d %H:%M:%S')
            end = datetime.datetime.strptime(end, '%Y-%m-%d %H:%M:%S')
            for gw_id in gw_ids:
                status, l = new_five_minitues_status(start, end, gw_id)
                lost = 0
                gantt_time = []
                if status == 5:
                    TbMachineGantt.objects.create(gw_id=gw_id, state=status, start_time=start, last_time=end,
                                                  nomal=l[0],
                                                  exception=l[1], standby=l[2], stop=l[3],
                                                  lost=l[4])
                else:
                    for i in l:
                        gantt_time.append(int(i.total_seconds()))
                    TbMachineGantt.objects.create(gw_id=gw_id, state=status, start_time=start, last_time=end,
                                                  nomal=gantt_time[0],
                                                  exception=gantt_time[1], standby=gantt_time[2], stop=gantt_time[3],
                                                  lost=lost)

            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def machine_nav(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            site = request.GET.get('site', '')
            building = request.GET.get('building', '')
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            # sql = "SELECT DISTINCT {0} FROM `tb_machine_state1` a LEFT JOIN st126_device b ON a.gw_id=b.gw_id  \
            #      WHERE customer='ST'{1} ORDER BY {0}"
            sql = "SELECT DISTINCT {0} FROM `st126_device` WHERE base_type='equipment' AND enable_flag=1 {1} ORDER BY {0}"
            if site == "":
                sql = sql.format('site', '')
            elif site and building == '' and floor == '':
                f_sql = "and site='{}'".format(site)
                sql = sql.format('building', f_sql)
            elif building and floor == '':
                f_sql = "and site='{}' and building='{}' ".format(site, building)
                sql = sql.format('floor', f_sql)
            elif floor and sections == '':
                f_sql = "and site='{}' and building='{}' and floor='{}' ".format(site, building, floor)
                sql = sql.format('sections', f_sql)
            elif sections and not provider:
                f_sql = "and site='{}' and building='{}' and floor='{}'and sections='{}' " \
                    .format(site, building, floor, sections)
                sql = sql.format('provider', f_sql)
            elif provider and not level:
                f_sql = "and site='{}' and building='{}' and floor='{}'and sections='{}' and provider='{}' " \
                    .format(site, building, floor, sections, provider)
                sql = sql.format('level_1', f_sql)
            elif level:
                f_sql = "and site='{}' and building='{}' and floor='{}'and sections='{}' and provider='{}' and level_1='{}' " \
                    .format(site, building, floor, sections, provider, level)
                sql = sql.format('gw_name', f_sql)
            data = my_select1(sql)
            data = [i[0] for i in data]
            content['payload'] = data
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def machine_gantt(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            gw_name = request.GET.get('gw_name', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            page = request.GET.get('page', '1')
            timeto = datetime.datetime.now().strftime("%Y-%m-%d")
            timeto2 = datetime.datetime.now()
            yesterday = timeto2 + datetime.timedelta(days=-1)
            if not sections and not gw_name:
                cat_sql = ''
            elif sections and not provider:
                cat_sql = "where  sections = '{}'".format(sections)
            elif provider and not level:
                cat_sql = " where sections = '{}' and provider= '{}'".format(sections, provider)
            elif level and not gw_name:
                cat_sql = "where  sections ='{}' and provider='{}' and level_1='{}'".format(sections, provider, level)
            elif gw_name:
                cat_sql = "where  sections ='{}' and provider='{}' and level_1='{}' and gw_name='{}'" \
                    .format(sections, provider, level, gw_name)
            if not start_time:
                sql = SQL['MACHINE_GANTT']
                timeto = timeto + " 00:00:00"
            else:
                sql = SQL['MACHINE_GANTT_DAY']
                timeto = start_time
            gantt_data = my_select1(sql.format(timeto, cat_sql))
            gw_list = []
            gw_dict = {}
            for i in gantt_data:
                if i[0] not in gw_list:
                    gw_list.append(i[0])
            total_page = len(gw_list)
            start_page = int(page) * 10 - 10
            end_page = int(page) * 10
            for i in gw_list[start_page:end_page]:
                gw_dict[i] = []
                state1 = 0  # 正常时间
                state2 = 0  # 异常时间
                state3 = 0  # 待机时间
                state4 = 0  # 停机时间
                state5 = 0  # 未知时间
                for j in gantt_data:
                    if i in j:
                        state1 += j[2]
                        state2 += j[3]
                        state3 += j[4]
                        state4 += j[5]
                        state5 += j[6]
                        con = {"state": j[1]}
                        gw_dict[i].append(con)
                gw_dict[i].append({'1': state1, '2': state2, '3': state3, '4': state4, '5': state5})
            content['payload'] = {'gantt_data': gw_dict, 'in_page': page, 'total_page': total_page}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def machine_trend(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            gw_name = request.GET.get('port', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            by_type = 'gw_name'
            if not start_time and not end_time:
                end_time = datetime.date.today()
                start_time = end_time - relativedelta(days=6)
            if not sections:
                cat_sql = ''
                by_type = 'sections'
            if floor and not sections:
                cat_sql = "and floor='%s'" % (floor)
            if sections and not provider:
                cat_sql = "and floor='%s' and sections='%s'" % (floor, sections)
            if provider and not level:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s'" % (floor, sections, provider)
            if level and not gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s'and level_1='%s'" % (
                    floor, sections, provider, level)
            if gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s'and level_1='%s' and gw_name='%s'" \
                          % (floor, sections, provider, level, gw_name)

            day_data = my_select1(SQL['JDL_DAY'].format(start_time, end_time, cat_sql))
            trend_data = my_select1(SQL['JDL_TREND'].format(by_type, start_time, end_time, cat_sql))
            content['payload'] = {"machine_day": day_data, "machine_trend": trend_data}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def machine_forms(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            gw_name = request.GET.get('port', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            if not start_time and not end_time:
                end_time = datetime.date.today()
                start_time = end_time - relativedelta(days=1)
                end_time = start_time
            if not floor:
                cat_sql = ''
            if floor and not sections:
                cat_sql = "and floor='%s'" % (floor)
            if floor and sections and not provider:
                cat_sql = "and floor='%s' and sections='%s'" % (floor, sections)
            if provider and not level:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s'" % (floor, sections, provider)
            if level and not gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s'and level_1='%s'" % (
                    floor, sections, provider, level)
            if gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s'and level_1='%s' and gw_name='%s'" \
                          % (floor, sections, provider, level, gw_name)
            machine_data = my_select1(SQL['MACHINE_FORMS'].format(start_time, end_time, cat_sql))
            content['payload'] = {"machine_data": machine_data}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def watt_nav(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            site = request.GET.get('site', '')
            building = request.GET.get('building', '')
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            sql = "SELECT DISTINCT {0} from(SELECT equipment_id FROM `sanse_deng_list` WHERE sensors_type='e_meter') a \
                    LEFT JOIN st126_device b ON a.equipment_id=b.gw_id where customer='ST'{1}"

            if site == "":
                sql = sql.format('site', '')
            if site and building == '' and floor == '':
                f_sql = "and site='{}'".format(site)
                sql = sql.format('building', f_sql)
            if building and floor == '':
                f_sql = "and site='{}' and building='{}' ".format(site, building)
                sql = sql.format('floor', f_sql)
            if floor and sections == '':
                f_sql = "and site='{}' and building='{}' and floor='{}' ".format(site, building, floor)
                sql = sql.format('sections', f_sql)
            if sections and not provider:
                f_sql = "and site='{}' and building='{}' and floor='{}'and sections='{}' " \
                    .format(site, building, floor, sections)
                sql = sql.format('provider', f_sql)
            if provider and not level:
                f_sql = "and site='{}' and building='{}' and floor='{}'and sections='{}' and provider='{}' " \
                    .format(site, building, floor, sections, provider)
                sql = sql.format('level_1', f_sql)
            if level:
                f_sql = "and site='{}' and building='{}' and floor='{}'and sections='{}' and provider='{}' and level_1='{}' " \
                    .format(site, building, floor, sections, provider, level)
                sql = sql.format('gw_name', f_sql)
            data = my_select1(sql)
            data = [i[0] for i in data]
            content['payload'] = data
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def watt_state(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            gw_name = request.GET.get('gw_name', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            timeto = datetime.datetime.now().strftime("%Y-%m-%d")
            timeto2 = datetime.datetime.now()
            yesterday = timeto2 + datetime.timedelta(days=-1)
            if (not start_time and not end_time) or int(timeto2.day) == int(start_time[8:10]):
                if timeto2.hour > 20 or timeto2.hour < 8:
                    if timeto2.hour < 8:
                        timeto = yesterday.strftime("%Y-%m-%d")
                    s = ' 19:30:00'
                    flag = "PN"
                else:
                    s = ' 07:30:00'
                    flag = "PD"
                state_sql = "start_time>='%s' " % (timeto + s)
                by_time = "rc"
                if not sections:
                    cat_sql = "rc>='%s'" % (timeto + s)
                if sections and not provider:
                    cat_sql = "rc>='%s' and sections='%s'" % (timeto + s, sections)
                if provider and not level:
                    cat_sql = "rc>='%s' and sections='%s' and provider='%s'" % (timeto + s, sections, provider)
                if level and not gw_name:
                    cat_sql = "rc>='%s' and sections='%s' and provider='%s'and level_1='%s'" % (
                        timeto + s, sections, provider, level)
                if gw_name:
                    cat_sql = "rc>='%s' and sections='%s' and provider='%s'and level_1='%s' and gw_name='%s'" \
                              % (timeto + s, sections, provider, level, gw_name)
            elif start_time and end_time:
                flag = "DAY"
                by_time = "DATE_FORMAT(rc,'%Y-%m-%d')"
                state_sql = "start_time>='%s' and start_time<='%s' " % (
                    start_time + " 00:00:00", end_time + " 23:59:59")
                start_time = start_time + " 00:00:00"
                end_time = end_time + " 23:59:59"
                if not sections:
                    cat_sql = "rc>='%s' and rc<='%s' " % (start_time, end_time)
                if sections and not provider:
                    cat_sql = "rc>='%s' and rc<='%s' and sections='%s'" % (start_time, end_time, sections)
                if provider and not level:
                    cat_sql = "rc>='%s'and rc<='%s' and sections='%s' and provider='%s'" % (
                        start_time, end_time, sections, provider)
                if level and not gw_name:
                    cat_sql = "rc>='%s'and rc<='%s' and sections='%s' and provider='%s'and level_1='%s'" \
                              % (start_time, end_time, sections, provider, level)
                if gw_name:
                    cat_sql = "rc>='%s' and rc<='%s' and sections='%s' and provider='%s'and level_1='%s' and gw_name='%s'" \
                              % (start_time, end_time, sections, provider, level, gw_name)
            energy_trend = my_select1(SQL['ENERGY_TREND'].format(by_time, cat_sql))
            total_energy = my_select1(SQL['TOTAL_ENERGY'].format(cat_sql))
            san_energy = my_select1(SQL['SAN_ENERGY_ID'].format(cat_sql))
            san_state = my_select1(SQL['SAN_STATE'].format(state_sql, cat_sql))
            san_state_id = my_select1(SQL['SAN_STATE_ID'].format(state_sql, cat_sql))
            san_state_id_list = [[i[0], i[1], i[2], i[3]] for i in san_state_id]
            if energy_trend:
                gw_list = []
                if by_time == 'rc':
                    energy_data = {gettime(i[0], 'hour'): i[1] for i in energy_trend}
                else:
                    energy_data = {i[0]: i[1] for i in energy_trend}
                if not gw_name:
                    for i in san_energy:
                        gw_conn = {}
                        gw_conn['total_energy'] = i[2]
                        for j in san_state_id_list:
                            if i[0] in j:
                                gw_conn['gw_id'] = i[1]
                                gw_conn['normal'] = i[2] * float(j[1])
                                gw_conn['unusual'] = i[2] * float(j[2])
                                gw_conn['working'] = i[2] * float(j[3])
                        gw_list.append(gw_conn)
                content['payload'] = {"total_energy": total_energy,
                                      "energy_trend": energy_data,
                                      "energy_state": [
                                          total_energy[0][0] if total_energy[0][0] else 0 * 0 if san_state[0][
                                                                                                     0] is None else float(
                                              san_state[0][0]),
                                          total_energy[0][0] if total_energy[0][0] else 0 * 0 if san_state[0][
                                                                                                     1] is None else float(
                                              san_state[0][1])],
                                      "energy_by_id": gw_list, "flag": flag}
            else:
                content['payload'] = []
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def watt_forms1(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            gw_name = request.GET.get('gw_name', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            timeto = datetime.datetime.now().strftime("%Y-%m-%d")
            timeto2 = datetime.datetime.now()
            yesterday = timeto2 + datetime.timedelta(days=-1)
            watt_forms_sql = "SELECT  concat(site,'_',building,'_',floor,'_',sections),provider,level_1, \
                           gw_id,gw_name,SUM(energy) FROM view_watt_by_hours1 WHERE {} {} GROUP BY gw_id"
            san_sql = "SELECT a.gw_id,IFNULL((SUM(nomal) + SUM(standby)) / SUM(300),0) , IFNULL((SUM(exception) + sum(STOP)) / SUM(300),0), \
                        IFNULL(SUM(nomal) / SUM(300), 0) FROM tb_machine_gantt a LEFT JOIN st126_device b ON a.gw_id=b.gw_id WHERE {} {} GROUP by gw_id"
            if (not start_time and not end_time) or int(timeto2.day) == int(start_time[8:10]):
                if timeto2.hour > 20 or timeto2.hour < 8:
                    if timeto2.hour < 8:
                        timeto = yesterday.strftime("%Y-%m-%d")
                    energy_time = "rc>='%s' " % (timeto + ' 19:30:00')
                    san_time = "start_time='%s'" % (timeto + ' 20:00:00')
                else:
                    energy_time = "rc>='%s' " % (timeto + ' 07:30:00')
                    san_time = "start_time>='%s'" % (timeto + ' 08:00:00')
            elif start_time and end_time:
                start_time = start_time + " 00:00:00"
                end_time = end_time + " 23:59:59"
                energy_time = "rc>='%s' and rc<='%s' " % (start_time, end_time)
                san_time = "start_time>='%s' and last_time<='%s'" % (start_time, end_time)
            if not floor:
                cat_sql = ''
            elif floor and not sections:
                cat_sql = "and floor='%s'" % (floor)
            elif sections and not provider:
                cat_sql = "and floor='%s' and sections='%s'" % (floor, sections)
            elif provider and not level:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s'" % (floor, sections, provider)
            elif level and not gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s' and level_1='%s' " \
                          % (floor, sections, provider, level)
            elif gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s' and level_1='%s' and gw_name='%s'" \
                          % (floor, sections, provider, level, gw_name)
            san_energy = my_select1(watt_forms_sql.format(energy_time, cat_sql))
            san_state_id = my_select1(san_sql.format(san_time, cat_sql))
            san_state_id_list = [[i[0], i[1], i[2], i[3]] for i in san_state_id]
            gw_list = []
            if not san_energy:
                content['payload'] = {}
            else:
                for i in san_energy:
                    gw_conn = {}
                    gw_conn['gw_name'] = i[4]
                    gw_conn['total_energy'] = i[5]
                    gw_conn['loca'] = i[0]
                    gw_conn['provider'] = i[1]
                    gw_conn['level'] = i[2]
                    for j in san_state_id_list:
                        if i[3] in j:
                            gw_conn['normal'] = i[5] * float(j[1])
                            gw_conn['unusual'] = i[5] * float(j[2])
                            gw_conn['working'] = i[5] * float(j[3])
                    gw_list.append(gw_conn)
                content['payload'] = gw_list
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def total_energy1(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            gw_name = request.GET.get('gw_name', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            timeto2 = datetime.datetime.now()
            timeto = datetime.datetime.now().strftime("%Y-%m-%d")
            yesterday = timeto2 + datetime.timedelta(days=-1)
            energy_sql = "SELECT  SUM(energy) FROM `tb_watt_by_hours1` a \
                         LEFT JOIN st126_device b ON a.gw_id=b.gw_id WHERE {} {}"
            san_sql = "SELECT (SUM(nomal) + SUM(standby)) / SUM(300),(SUM(exception) + sum(STOP)) / SUM(300) FROM \
                      tb_machine_gantt a LEFT JOIN st126_device b ON a.gw_id = b.gw_id WHERE {} {} "
            if (not start_time and not end_time) or int(timeto2.day) == int(start_time[8:10]):
                timeto = yesterday.strftime("%Y-%m-%d")
                energy_time = "rc>='%s' " % (timeto + ' 23:30:00')
                san_time = "start_time>='%s'" % (timeto + ' 23:00:00')
            else:
                start_time = start_time + " 00:00:00"
                end_time = end_time + " 23:59:59"
                energy_time = "rc>='%s' and rc<='%s' " % (start_time, end_time)
                san_time = "start_time>='%s' and last_time<='%s'" % (start_time, end_time)
            if not floor:
                cat_sql = ''
            elif floor and not sections:
                cat_sql = "and floor='%s'" % (floor)
            elif sections and not provider:
                cat_sql = "and floor='%s' and sections='%s'" % (floor, sections)
            elif provider and not level:
                cat_sql = "and floor='%s'and sections='%s' and provider='%s'" % (floor, sections, provider)
            elif level and not gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s' and level_1='%s' " \
                          % (floor, sections, provider, level)
            elif gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s' and level_1='%s' and gw_name='%s'" \
                          % (floor, sections, provider, level, gw_name)
            energy_data = my_select1(energy_sql.format(energy_time, cat_sql))
            san_data = my_select1(san_sql.format(san_time, cat_sql))
            if san_data[0][0] and energy_data[0][0]:
                content['payload'] = {'total_energy': energy_data[0][0],
                                      'energy_state': [float(san_data[0][0]) * energy_data[0][0],
                                                       float(san_data[0][1]) * energy_data[0][0]]}
            elif not san_data[0][0] and energy_data[0][0]:
                content['payload'] = {'total_energy': energy_data[0][0],
                                      'energy_state': [0, 0]}
            elif not energy_data[0][0] and san_data[0][0]:
                content['payload'] = {'total_energy': 0,
                                      'energy_state': [0, 0]}
            elif not san_data[0][0] and not energy_data[0][0]:
                content['payload'] = {'total_energy': 0,
                                      'energy_state': [0, 0]}
            # content['payload']['flag']=flag
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def energy_trend1(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            gw_name = request.GET.get('gw_name', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            timeto2 = datetime.datetime.now()
            timeto = datetime.datetime.now().strftime("%Y-%m-%d")
            yesterday = timeto2 + datetime.timedelta(days=-1)
            trend_sql = "SELECT {0},SUM(energy) FROM view_watt_by_hours1 WHERE {1} {2}  GROUP BY {0}"
            by_time = 'rc'
            if (not start_time and not end_time) or int(timeto2.day) == int(start_time[8:10]):
                timeto = yesterday.strftime("%Y-%m-%d")
                energy_time = "rc>='%s' " % (timeto + ' 23:30:00')
                flag = 'PN'
            else:
                if start_time == end_time:
                    start_time = start_time + " 00:00:00"
                    end_time = end_time + " 23:59:59"
                    energy_time = "rc>='%s' and rc<='%s' " % (start_time, end_time)
                    flag = 'day'
                else:
                    start_time = start_time + " 00:00:00"
                    end_time = end_time + " 23:59:59"
                    by_time = 'date(rc)'
                    energy_time = "rc>='%s' and rc<='%s' " % (start_time, end_time)
                    flag = 'day'
            if not floor:
                cat_sql = ''
            elif floor and not sections:
                cat_sql = "and floor='%s'" % (floor)
            elif sections and not provider:
                cat_sql = "and floor='%s' and sections='%s'" % (floor, sections)
            elif provider and not level:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s'" % (floor, sections, provider)
            elif level and not gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s' and level_1='%s' " \
                          % (floor, sections, provider, level)
            elif gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s' and level_1='%s' and gw_name='%s'" \
                          % (floor, sections, provider, level, gw_name)
            trend_data = my_select1(trend_sql.format(by_time, energy_time, cat_sql))
            energy_data1 = {}
            if trend_data:
                if by_time == 'rc':
                    energy_data1 = {gettime(i[0], 'hour'): i[1] for i in trend_data}
                else:
                    energy_data1 = {str(i[0]): i[1] for i in trend_data}

            content['payload'] = {'energy_trend': energy_data1, 'flag': flag}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def energy_id1(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            gw_name = request.GET.get('gw_name', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            timeto2 = datetime.datetime.now()
            timeto = datetime.datetime.now().strftime("%Y-%m-%d")
            yesterday = timeto2 + datetime.timedelta(days=-1)
            san_sql = "SELECT a.gw_id,IFNULL((SUM(nomal) + SUM(standby)) / SUM(300),0) , IFNULL((SUM(exception) + sum(STOP)) / SUM(300),0), \
            IFNULL(SUM(nomal) / SUM(300), 0) FROM tb_machine_gantt a LEFT JOIN st126_device b ON a.gw_id=b.gw_id WHERE {} {} GROUP by gw_id"
            energy_sql = "SELECT gw_id,gw_name,SUM(energy) FROM view_watt_by_hours1 WHERE {} {} GROUP BY gw_id ORDER BY SUM(energy) DESC"
            if (not start_time and not end_time) or int(timeto2.day) == int(start_time[8:10]):
                if timeto2.hour > 20 or timeto2.hour < 8:
                    if timeto2.hour < 8:
                        timeto = yesterday.strftime("%Y-%m-%d")
                    energy_time = "rc>='%s' " % (timeto + ' 19:30:00')
                    san_time = "start_time='%s'" % (timeto + ' 20:00:00')
                else:
                    energy_time = "rc>='%s' " % (timeto + ' 07:30:00')
                    san_time = "start_time>='%s'" % (timeto + ' 08:00:00')
            elif start_time and end_time:
                start_time = start_time + " 00:00:00"
                end_time = end_time + " 23:59:59"
                energy_time = "rc>='%s' and rc<='%s' " % (start_time, end_time)
                san_time = "start_time>='%s' and last_time<='%s'" % (start_time, end_time)
            if not floor:
                cat_sql = ''
            elif floor and not sections:
                cat_sql = "and floor='%s'" % (floor)
            elif sections and not provider:
                cat_sql = "and floor='%s' and sections='%s'" % (floor, sections)
            elif provider and not level:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s'" % (floor, sections, provider)
            elif level and not gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s' and level_1='%s'" \
                          % (floor, sections, provider, level)
            elif gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s' and level_1='%s' and gw_name='%s'" \
                          % (floor, sections, provider, level, gw_name)
            san_data = my_select1(san_sql.format(san_time, cat_sql))
            energy_data = my_select1(energy_sql.format(energy_time, cat_sql))
            san_id_list = [[i[0], i[1], i[2], i[3]] for i in san_data]
            gw_list = []
            if energy_data:
                for i in energy_data:
                    gw_conn = {}
                    gw_conn['gw_id'] = i[1]
                    gw_conn['total_energy'] = i[2]
                    for j in san_id_list:
                        if i[0] in j:
                            gw_conn['normal'] = round(i[2] * float(j[1]), 2)
                            gw_conn['unusual'] = round(i[2] * float(j[2]), 2)
                            gw_conn['working'] = round(i[2] * float(j[3]), 2)
                    gw_list.append(gw_conn)
                content['payload'] = gw_list
            else:
                content['payload'] = {}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def energy_top(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            gw_name = request.GET.get('gw_name', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            timeto2 = datetime.datetime.now()
            timeto = datetime.datetime.now().strftime("%Y-%m-%d")
            yesterday = timeto2 + datetime.timedelta(days=-1)
            san_sql = "SELECT a.gw_id,IFNULL((SUM(nomal) + SUM(standby)) / SUM(300),0) , IFNULL((SUM(exception) + sum(STOP)) / SUM(300),0), \
            IFNULL(SUM(nomal) / SUM(300), 0) FROM tb_machine_gantt a LEFT JOIN st126_device b ON a.gw_id=b.gw_id WHERE {} {} GROUP by gw_id"
            energy_sql = "SELECT gw_id,gw_name,SUM(energy) FROM view_watt_by_hours1 WHERE {} {} GROUP BY gw_id ORDER BY SUM(energy) DESC"
            if (not start_time and not end_time) or int(timeto2.day) == int(start_time[8:10]):
                if timeto2.hour > 20 or timeto2.hour < 8:
                    if timeto2.hour < 8:
                        timeto = yesterday.strftime("%Y-%m-%d")
                    energy_time = "rc>='%s' " % (timeto + ' 19:30:00')
                    san_time = "start_time='%s'" % (timeto + ' 20:00:00')
                else:
                    energy_time = "rc>='%s' " % (timeto + ' 07:30:00')
                    san_time = "start_time>='%s'" % (timeto + ' 08:00:00')
            elif start_time and end_time:
                start_time = start_time + " 00:00:00"
                end_time = end_time + " 23:59:59"
                energy_time = "rc>='%s' and rc<='%s' " % (start_time, end_time)
                san_time = "start_time>='%s' and last_time<='%s'" % (start_time, end_time)
            if not floor:
                cat_sql = ''
            elif floor and not sections:
                cat_sql = "and floor='%s'" % (floor)
            elif sections and not provider:
                cat_sql = "and floor='%s' and sections='%s'" % (floor, sections)
            elif provider and not level:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s'" % (floor, sections, provider)
            elif level and not gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s' and level_1='%s' " \
                          % (floor, sections, provider, level)
            elif gw_name:
                cat_sql = "and floor='%s' and sections='%s' and provider='%s' and level_1='%s' and gw_name='%s'" \
                          % (floor, sections, provider, level, gw_name)
            san_data = my_select1(san_sql.format(san_time, cat_sql))
            energy_data = my_select1(energy_sql.format(energy_time, cat_sql))
            san_id_list = [[i[0], i[1], i[2], i[3]] for i in san_data]
            stop_top = {}
            nomal = {}
            if energy_data and san_id_list:
                for i in energy_data:
                    for j in san_id_list:
                        if i[0] in j:
                            stop_top[i[1]] = round(i[2] * float(j[2]), 2)
                            nomal[i[1]] = round(i[2] * float(j[3]), 2)
                data = list(zip(*sorted(stop_top.items(), key=lambda student: student[1])[-int(5):][::-1]))
                data1 = list(zip(*sorted(nomal.items(), key=lambda student: student[1])[-int(10):][::-1]))
                content['payload'] = {"stop_top": [data[0], data[1]], "nomal_top": [data1[0], data1[1]]}
            else:
                content['payload'] = {}
            content['status'] = 1
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


# 实时看板接口
@api_view(http_method_names=["GET"])
def machine_board(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            nomal = 0
            exception = 0
            standby = 0
            stop = 0
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            gw_sql = "SELECT gw_id FROM `st126_device` WHERE enable_flag = 1 AND \
                      base_type = 'equipment' AND customer = 'ST' AND {} ORDER BY gw_name"
            if not floor:
                cat_sql = '1=1'
            if floor and not sections:
                cat_sql = "floor='%s'" % (floor)
            if sections:
                cat_sql = "floor='%s' and sections='%s'" % (floor, sections)
            gw_id = my_select1(gw_sql.format(cat_sql))
            if gw_id:
                for i in gw_id:
                    san_data = red_conn("st:machine:" + i[0])
                    if san_data:
                        ps_date = eval(san_data)
                        if ps_date['green'] == '1':
                            nomal += 1
                        elif ps_date['yellow'] == '1':
                            standby += 1
                        elif ps_date['red'] == '1':
                            exception += 1
                        else:
                            stop += 1
                    else:
                        device_state = red_conn("st:device_state:" + i[0])
                        if device_state:
                            ps_date = eval(device_state)
                            if ps_date['state'] == 1:
                                nomal += 1
                            elif ps_date['state'] == 2:
                                standby += 1
                            elif ps_date['state'] == 3:
                                exception += 1
                            else:
                                stop += 1
                        else:
                            meter_data = red_conn("st:meter:" + i[0])
                            if meter_data:
                                ps_date = eval(meter_data)
                                if float(ps_date['a_current']) > 0.5:
                                    nomal += 1
                                else:
                                    stop += 1
                            else:
                                stop += 1
            content['payload'] = {'nomal': nomal, 'exception': exception, 'standby': standby, 'stop': stop}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


# 实时看板接口
@api_view(http_method_names=["GET"])
def layout_board(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            sections = request.GET.get('sections', '')
            if not sections: raise Exception(u"請傳入車間名稱")
            gw_sql = "SELECT gw_name,gw_id FROM `st126_device` WHERE enable_flag = 1 AND \
                      base_type = 'equipment' AND customer = 'ST' AND {} ORDER BY gw_name"
            if sections:
                cat_sql = "sections='%s'" % (sections)
            gw_id = my_select1(gw_sql.format(cat_sql))
            state_data = []
            if gw_id:
                for i in gw_id:
                    layout_data = {}
                    layout_data['gw_name'] = i[0]
                    layout_data['gw_id'] = i[1]
                    san_data = red_conn("st:machine:" + i[1])
                    if san_data:
                        ps_date = eval(san_data)
                        data = {"Red": int(ps_date['red']), "Green": int(ps_date['green']),
                                "Yellow": int(ps_date['yellow'])}
                    else:
                        device_state = red_conn("st:device_state:" + i[1])
                        if device_state:
                            ps_date = eval(device_state)
                            if ps_date['state'] == 1:
                                data = {"Red": 0, "Green": 1, "Yellow": 0}
                            elif ps_date['state'] == 2:
                                data = {"Red": 0, "Green": 0, "Yellow": 1}
                            elif ps_date['state'] == 3:
                                data = {"Red": 1, "Green": 0, "Yellow": 0}
                            else:
                                data = {"Red": 0, "Green": 0, "Yellow": 0}
                        else:
                            meter_data = red_conn("st:meter:" + i[1])
                            if meter_data:
                                ps_date = eval(meter_data)
                                if float(ps_date['a_current']) > 0.5:
                                    data = {"Red": 0, "Green": 1, "Yellow": 0}
                                else:
                                    data = {"Red": 0, "Green": 0, "Yellow": 0}
                            else:
                                data = {"Red": 0, "Green": 0, "Yellow": 0}
                    layout_data['data'] = data
                    state_data.append(layout_data)
            content['payload'] = state_data
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


# 3D稼動率
@api_view(http_method_names=["GET"])
def layout_activation(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            gw_name = request.GET.get('gw_name', '')
            if not gw_name: raise Exception(u"gw_name")
            timeto = datetime.date.today()
            gw_sql = "SELECT gw_name,FORMAT(SUM(rt)*100 / sum(ot),2) FROM `tb_meter_activation_by_day` a \
                    LEFT JOIN st126_device b ON a.gw_id = b.gw_id WHERE enable_flag = 1 AND rc = '{}' AND gw_name = '{}'"
            san_sql = "SELECT gw_name,FORMAT(sum(rt)*100/sum(ot),2) FROM `tb_activation_by_day1` a \
                      LEFT JOIN st126_device b ON a.gw_id = b.gw_id WHERE enable_flag = 1 AND rc = '{}' AND gw_name = '{}'"
            gw_jd = my_select1(san_sql.format(timeto, gw_name))
            activation = {}
            if gw_jd[0][0]:
                for i in gw_jd:
                    activation['use_rate'] = i[1]
                    activation['gw_name'] = i[0]
            else:
                gw_jd = my_select1(gw_sql.format(timeto, gw_name))
                if gw_jd[0][0]:
                    for i in gw_jd:
                        activation['use_rate'] = i[1]
                        activation['gw_name'] = i[0]
            content['payload'] = activation
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def th_board(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            avg_th = "SELECT  FORMAT(AVG(tem),2),FORMAT(avg(hum),2) FROM `tb_th_state` a  \
                     LEFT JOIN st126_device b on a.controller =b.gw_name where {}"
            gw_sql = "SELECT gw_id,gw_name FROM `sanse_deng_list` a LEFT JOIN st126_device b ON a.equipment_id = b.gw_id \
                     WHERE sensors_type = 'temp_hum_sensor' {} ORDER by gw_name"
            # defect_tem = "SELECT sections,gw_name,tem FROM `tb_th_state` a \
            #              LEFT JOIN st126_device b on a.controller =b.gw_name WHERE   a.tem<19 or a.tem>25 and {} and sections is not null GROUP BY sections,gw_name"
            # defect_hum = "SELECT sections,gw_name,hum FROM `tb_th_state` a \
            #              LEFT JOIN st126_device b on a.controller =b.gw_name WHERE  a.hum>65 and  {} and sections is not NULL GROUP BY sections,gw_name"
            if not floor:
                cat_sql = ''
            if floor and not sections:
                cat_sql = "and floor='%s'" % (floor)
            if sections:
                cat_sql = "and floor='%s' and sections='%s'" % (floor, sections)
            gw_data = my_select1(gw_sql.format(cat_sql))
            tem_avg = []
            hum_avg = []
            tem_defect = OrderedDict()
            hum_defect = OrderedDict()
            th_data = []
            if gw_data:
                for i in gw_data:
                    re_data = red_conn("st:th:" + i[0])
                    if re_data:
                        ps_date = eval(re_data)
                        tem_avg.append(float(ps_date['tem']))
                        hum_avg.append(float(ps_date['hum']))
                        if float(ps_date['tem']) < 19 or float(ps_date['tem']) > 28.5:
                            tem_defect[i[1]] = ps_date['tem']
                        if float(ps_date['hum']) > 65:
                            hum_defect[i[1]] = ps_date['hum']
                if hum_avg:
                    th_data = [round(sum(tem_avg) / len(tem_avg), 1), round(sum(hum_avg) / len(hum_avg), 1)]
            # tem_data = my_select1(defect_tem.format(cat_sql))
            # hum_data = my_select1(defect_hum.format(cat_sql))
            # tem_list = [i[0] for i in tem_data]
            # hum_list = [i[0] for i in hum_data]
            # tem_dict = OrderedDict()
            # hum_dict = OrderedDict()
            # for x in tem_list:
            #     my_dict = []
            #     for y in tem_data:
            #         if y[0] == x:
            #             my_dict.append([y[1], y[2]])
            #     tem_dict[x] = my_dict
            # for x in hum_list:
            #     my_dict = []
            #     for y in hum_data:
            #         if y[0] == x:
            #             my_dict.append([y[1],y[2]])
            #     hum_dict[x] = my_dict
            content['payload'] = {'avg_th': th_data, 'defect_tem': tem_defect, 'defect_hum': hum_defect}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def watt_board(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            timeto = datetime.date.today()
            # 查詢出每個id的能耗和狀態詳情然後各自累計
            energy_sql = "SELECT sum(e.nom_energy),IFNULL(sum(e.t_energy), 0)-sum(e.nom_energy),IFNULL(sum(e.t_energy), 0)\
                FROM (SELECT c.sections,c.gw_name,c.floor,a.total_energy -IF ((a.total_energy * b.jd),(a.total_energy * b.jd),0) nom_energy,\
                (a.total_energy) t_energy FROM (SELECT gw_id,sum(energy) total_energy FROM `tb_watt_by_day`\
                WHERE rc >= '{0}' AND rc <= '{1}' GROUP BY gw_id) a LEFT JOIN ( SELECT gw_id, (sum(ot) - sum(rt)) / sum(ot) jd \
                FROM tb_activation_by_day1 WHERE rc >= '{0}' AND rc <= '{1}' GROUP BY gw_id ) b ON a.gw_id = b.gw_id \
                LEFT JOIN st126_device c ON a.gw_id = c.gw_id GROUP BY c.gw_name ) e WHERE {2}"

            if not floor:
                cat_sql = '1=1'
            if floor and not sections:
                cat_sql = "floor='%s'" % (floor)
            if sections:
                cat_sql = "floor='%s' and sections='%s'" % (floor, sections)
            energy_data = my_select1(energy_sql.format(timeto, timeto, cat_sql))
            t_energy = {'total_energy': 0, 'energy_state': [0, 0]}
            if energy_data[0][2]:
                t_energy = {}
                for i in energy_data:
                    t_energy['total_energy'] = i[2]
                    t_energy['energy_state'] = [i[0], i[1]]
            content['payload'] = t_energy
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)



@api_view(http_method_names=["GET"])
def defect_nav(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            site = request.GET.get('site', '')
            building = request.GET.get('building', '')
            floor = request.GET.get('floor', '')
            sql = "SELECT DISTINCT {0} FROM `st126_device` WHERE customer='ST' and {0}!='NA' AND enable_flag=1 {1}"
            if site == "":
                sql = sql.format('site', '')
            if site and building == '':
                f_sql = "and site='{}'".format(site)
                sql = sql.format('building', f_sql)
            if building and floor == '':
                f_sql = "and site='{}' and building='{}' ".format(site, building)
                sql = sql.format('floor', f_sql)
            if floor:
                f_sql = "and site='{}' and building='{}' and floor='{}' ".format(site, building, floor)
                sql = sql.format('sections', f_sql)
            data = my_select1(sql)
            content['payload'] = [i[0] for i in data]
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def defect_total(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            timeto = datetime.date.today()
            sql = "SELECT defect_type,count(*) FROM tb_iot_defect a LEFT JOIN st126_device b ON a.gw_id=b.gw_id \
                  WHERE total_time>=300 and  date(end_time)>='{}' and date(end_time)<='{}' GROUP BY defect_type"
            if not start_time and not end_time:
                str_time = timeto - relativedelta(days=6)
                start_time = str_time
                end_time = timeto
                san_time = "start_time>='%s' and end_time<='%s'" % (start_time, end_time)
            data = my_select1(sql.format(start_time, end_time))
            content['payload'] = dict(data)
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def defect_status(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            timeto = datetime.date.today()
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            sql = "SELECT flag,count(flag) FROM `tb_iot_defect` WHERE  \
                   total_time >= 300 AND DATE(end_time) >= '{}' AND DATE(end_time) <= '{}'\
                   GROUP BY flag ORDER BY flag"
            if not start_time and not end_time:
                str_time = timeto - relativedelta(days=6)
                start_time = str_time
                end_time = timeto
                san_time = "end_time>='%s' and end_time<='%s'" % (start_time, end_time)
            data = my_select1(sql.format(start_time, end_time))
            content['payload'] = dict(data)
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


# 異常詳情表單
@api_view(http_method_names=["GET"])
def defect_forms(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            flag = request.GET.get('status', '')
            timeto = datetime.date.today()
            sql = "SELECT a.id,defect_type,gw_name,start_time,end_time,total_time,flag,manager \
                   FROM tb_iot_defect a LEFT JOIN st126_device b ON a.gw_id = b.gw_id WHERE total_time >= 300 \
                  AND DATE(end_time)>='{}' AND DATE(end_time)<='{}' {}  ORDER BY end_time DESC"
            if not start_time and not end_time:
                start_time = timeto - relativedelta(days=6)
                end_time = timeto
            if not floor:
                cat_sql = ''
            if floor and not sections:
                cat_sql = "and floor='%s'" % (floor)
            if sections:
                cat_sql = "and floor='%s' and sections='%s'" % (floor, sections)
            data = my_select1(sql.format(start_time, end_time, cat_sql))
            data1 = []
            for row in data:
                data_row = list(row)
                data_row[3] = data_row[3].strftime("%Y-%m-%d %H:%M:%S")
                data_row[4] = data_row[4].strftime("%Y-%m-%d %H:%M:%S")
                data1.append(data_row)
            content['payload'] = data1
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


# 異常詳情表單
@api_view(http_method_names=["GET", "POST"])
def defect_mat(request, pk):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            defect_desc = "SELECT a.id,gw_name,defect_type,start_time,end_time,total_time,flag,manager,operator,a.site,org,phone,\
                          operator_time,defect_desc,remark FROM `tb_iot_defect` a\
                          LEFT JOIN st126_device b ON a.gw_id = b.gw_id WHERE a.id='{}'"
            defect_detail = my_select1(defect_desc.format(pk))
            for i in defect_detail:
                df_msg = {}
                df_msg['id'] = i[0]
                df_msg['gw_name'] = i[1]
                df_msg['defect_type'] = i[2]
                df_msg['start_time'] = i[3].strftime("%Y-%m-%d %H:%M:%S")
                df_msg['end_time'] = i[4].strftime("%Y-%m-%d %H:%M:%S")
                df_msg['total_time'] = i[5]
                df_msg['flag'] = i[6]
                df_msg['manager'] = i[7]
                df_msg['operator'] = i[8]
                df_msg['site'] = i[9]
                df_msg['org'] = i[10]
                df_msg['phone'] = i[11]
                df_msg['operator_time'] = i[12]
                df_msg['defect_desc'] = i[13]
                df_msg['remark'] = i[14]
            content['payload'] = df_msg
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)

    elif request.method == 'POST':
        content = {'errmsg': ''}
        try:
            data = request.data
            user = request.user
            site = str(request.user.site)
            phone = request.user.short_number
            time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            instal_dft = "update `tb_iot_defect`set operator='{}',operator_time='{}',flag='2',site='{}', \
                         org='{}',phone='{}',defect_desc='{}',remark='{}' where id={} "
            insert_data(
                instal_dft.format(user, time, site, data['org'], phone, data['defect_desc'], data['remark'], pk))
            content['payload'] = 'OK'
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def defect_top(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            gw_name = request.GET.get('gw_name', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            timeto2 = datetime.datetime.now()
            timeto = datetime.datetime.now().strftime("%Y-%m-%d")
            tomorrow = (timeto2 + datetime.timedelta(days=6)).strftime("%Y-%m-%d")
            sql = "SELECT gw_name,sum(total_time) FROM `tb_iot_defect` a LEFT JOIN st126_device b ON a.gw_id=b.gw_id \
                 WHERE total_time>=300 and defect_type='machine' and  {} {} GROUP BY gw_name ORDER BY sum(total_time) DESC limit 5"
            if (not start_time and not end_time) or start_time == end_time:
                flag = "day"
                san_time = "end_time>='%s' and end_time<'%s' " % (timeto + ' 00:00:00', tomorrow + ' 00:00:00')
            elif start_time and end_time:
                flag = "day"
                start_time = start_time + " 00:00:00"
                end_time = end_time + " 23:59:59"
                san_time = "end_time>='%s' and end_time<='%s'" % (start_time, end_time)
            if not floor:
                cat_sql = ''
            if floor and not sections:
                cat_sql = "and floor='%s'" % (floor)
            if sections:
                cat_sql = "and floor='%s' and sections='%s'" % (floor, sections)
            if gw_name:
                cat_sql = "and gw_name='%s'" % (gw_name)
                sql = "SELECT a.id,defect_type,gw_name,start_time,end_time,total_time,flag,manager \
                        FROM tb_iot_defect a LEFT JOIN st126_device b ON a.gw_id = b.gw_id \
                        WHERE total_time >= 300  AND defect_type='machine' AND {} {} ORDER BY end_time DESC"
            data = my_select1(sql.format(san_time, cat_sql))

            if not gw_name:
                gw_name = [i[0] for i in data]
                defect_total = [i[1] for i in data]
                content['payload'] = {"gw_name": gw_name, "defect_total": defect_total}
            else:
                data1 = []
                for row in data:
                    data_row = list(row)
                    data_row[3] = data_row[3].strftime("%Y-%m-%d %H:%M:%S")
                    data_row[4] = data_row[4].strftime("%Y-%m-%d %H:%M:%S")
                    data1.append(data_row)
                content['payload'] = data1
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def defect_ternd(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            timeto2 = datetime.datetime.now()
            timeto = datetime.datetime.now().strftime("%Y-%m-%d")
            tomorrow = (timeto2 + datetime.timedelta(days=1)).strftime("%Y-%m-%d")
            by_time = "DATE_FORMAT(start_time,'%H:00')"
            sql = "SELECT {0},defect_type,count(defect_type) FROM `tb_iot_defect` a LEFT JOIN st126_device b ON a.gw_id=b.gw_id \
                    WHERE total_time>=300 and {1} {2} GROUP BY {0},defect_type ORDER BY {0} "
            if (not start_time and not end_time) or start_time == end_time:
                flag = "hours"
                energy_time = "end_time>='%s' and end_time<'%s' " % (timeto + ' 00:00:00', tomorrow + ' 00:00:00')
            elif start_time and end_time:
                start_time = start_time + " 00:00:00"
                end_time = end_time + " 23:59:59"
                by_time = 'date(end_time)'
                energy_time = "end_time>='%s' and end_time<='%s' " % (start_time, end_time)
                flag = 'day'
            if not floor:
                cat_sql = ''
            if floor and not sections:
                cat_sql = "and floor='%s'" % (floor)
            if sections:
                cat_sql = "and floor='%s' and sections='%s'" % (floor, sections)
            data = my_select1(sql.format(by_time, energy_time, cat_sql))
            data2 = [[str(i[0]), i[1], i[2]] for i in data]
            defect_dict = {str(i[0]) + str(i[1]): i[2] for i in data2}
            defect_list = []
            date_list = []
            cz_sec = {}
            for i in data2:
                if i[1] not in defect_list:
                    defect_list.append(i[1])
            for i in data2:
                if i[0] not in date_list:
                    date_list.append(i[0])
            for i in defect_list:
                date_dict = {}
                for j in date_list:
                    if j + i in defect_dict:
                        date_dict[j] = defect_dict[j + i]
                    else:
                        date_dict[j] = 0
                cz_sec[i] = date_dict
            content['payload'] = {"defect_ternd": cz_sec, "flag": flag}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def machine_top(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            stop_sql = "select equipment_name,sum({}) as time_total from \
                      (SELECT gw_id,stop,standby,exception FROM `tb_machine_gantt` where start_time>='{}') \
                    a LEFT JOIN (SELECT *FROM sanse_deng_list WHERE sensors_type = 'e_meter') b  \
                    ON a.gw_id=b.equipment_id  GROUP BY a.gw_id \
                    HAVING time_total>0 AND equipment_name is not null ORDER BY time_total DESC limit 5"
            top_op = ['stop', 'standby', 'exception']
            timeto = datetime.datetime.now().strftime("%Y-%m-%d 00:00:00")
            top_sum = OrderedDict()
            for i in top_op:
                data = my_select1(stop_sql.format(i, timeto))
                top_sum[i] = dict(data)
            content['payload'] = top_sum
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def state_detail(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            sections = request.GET.get('sections', '')
            num_page = request.GET.get('page', '1')
            s_state = request.GET.get('s_state', '')
            se_sql = "SELECT gw_id,gw_name,provider,level_1,asset_no,img_url FROM `st126_device` \
                    WHERE sections='{}' AND enable_flag = 1 AND base_type = 'equipment'AND customer = 'ST' ORDER BY gw_name"
            if not sections: raise Exception(u"請傳入車間名稱")
            ma_data = my_select1(se_sql.format(sections))
            ma_sum = []
            state = '4'
            for i in ma_data:
                ma_detail = {}
                ma_detail['gw_name'] = i[1]
                ma_detail['provider'] = i[2]
                ma_detail['level'] = i[3]
                ma_detail['ccbh'] = i[4]
                ma_detail['img_url'] = i[5]
                ma_detail['state'] = '4'
                re_data = red_conn("st:machine:" + i[0])
                if re_data:
                    san_data = eval(re_data)
                    if san_data['green'] == '1':
                        state = '1'
                    elif san_data['yellow'] == '1':
                        state = '2'
                    elif san_data['red'] == '1':
                        state = '3'
                    else:
                        state = '4'
                else:
                    device_state = red_conn("st:device_state:" + i[0])
                    if device_state:
                        ps_date = eval(device_state)
                        if ps_date['state'] == 1:
                            state = '1'
                        elif ps_date['state'] == 2:
                            state = '2'
                        elif ps_date['state'] == 3:
                            state = '3'
                        else:
                            state = '4'
                    else:
                        re_data = red_conn("st:meter:" + i[0])
                        if re_data:
                            ps_date = eval(re_data)
                            if float(ps_date['a_current']) >= 0.5:
                                state = '1'
                            else:
                                state = '4'
                        else:
                            state = '4'
                ma_detail['state'] = state
                ma_sum.append(ma_detail)
            ma_sum1 = []
            if s_state:
                for i in ma_sum:
                    if i['state'] == str(s_state):
                        ma_sum1.append(i)
                ma_sum = ma_sum1
            if num_page:
                pag_class = Paginator(ma_sum, 10)
            else:
                pag_class = Paginator(ma_sum, len(ma_sum) + 1)
                num_page = 1
            select_data = pag_class.page(num_page)
            content['payload'] = {'machine_data': select_data.object_list, 'in_page': int(num_page),
                                  'total_page': pag_class.count}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def machine_total(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            tot_sql = "SELECT sections,count(*) FROM `st126_device` WHERE base_type = 'equipment' \
                       AND enable_flag = 1 AND customer = 'ST'GROUP BY sections"
            machine_data = my_select1(tot_sql)
            content['payload'] = dict(machine_data)
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def se_state(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            se_sql = "SELECT DISTINCT sections FROM `st126_device` WHERE  customer='ST' AND enable_flag=1 ORDER BY right(sections,4)"
            gw_sql = "SELECT gw_id FROM `st126_device` WHERE base_type = 'equipment' AND enable_flag = 1  \
                     AND customer = 'ST' and sections='{}' "
            se_data = my_select1(se_sql)
            se_total = OrderedDict()
            if se_data:
                for i in se_data:
                    nom = 0
                    gw_data = my_select1(gw_sql.format(i[0]))
                    total_gw = len(gw_data)
                    for j in gw_data:
                        re_data = red_conn("st:machine:" + j[0])
                        if re_data:
                            san_data = eval(re_data)
                            if san_data['green'] == '1' or san_data['red'] == '1' or san_data['yellow'] == '1':
                                nom += 1
                            else:
                                pass
                        else:
                            device_state = red_conn("st:device_state:" + j[0])
                            if device_state:
                                ps_date = eval(device_state)
                                if ps_date['state'] == 1 or ps_date['state'] == 2 or ps_date['state'] == 3:
                                    nom += 1
                                else:
                                    pass
                            else:
                                re_data = red_conn("st:meter:" + j[0])
                                if re_data:
                                    ps_date = eval(re_data)
                                    if float(ps_date['a_current']) > 0.5:
                                        nom += 1
                    se_total[i[0]] = [total_gw, nom]
            content['payload'] = se_total
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def machine_gantt1(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            gw_name = request.GET.get('gw_name', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            page = request.GET.get('page', '1')
            timeto = datetime.datetime.now().strftime("%Y-%m-%d")
            timeto2 = datetime.datetime.now()
            yesterday = timeto2 + datetime.timedelta(days=-1)
            if not sections: raise Exception(u"請傳入車間名稱")
            if not provider: raise Exception(u"請傳入設備品牌")
            if not level: raise Exception(u"請傳入設備型號")
            if not gw_name: raise Exception(u"請傳入機台名稱")
            cat_sql = "sections ='{}' and provider='{}' and level_1='{}' and gw_name='{}'" \
                .format(sections, provider, level, gw_name)
            if not start_time:
                sql = SQL['SAN_GANTT']
                sql1 = SQL['MACHINE_GANTT1']
                timeto = timeto + " 00:00:00"
            else:
                sql = SQL['SAN_GANTT_DAY']
                sql1 = SQL['MACHINE_GANTT_DAY1']
                timeto = start_time
            state1 = 0  # 正常时间
            state2 = 0  # 异常时间
            state3 = 0  # 待机时间
            state4 = 0  # 停机时间
            state5 = 0  # 未知时间
            gw_dict = {}
            gantt_data = my_select1(sql.format(timeto, cat_sql))
            if gantt_data:
                gw_dict[gantt_data[0][0]] = []
                for i in gantt_data:
                    state1 += i[2]
                    state2 += i[3]
                    state3 += i[4]
                    state4 += i[5]
                    state5 += i[6]
                    con = {"state": i[1]}
                    gw_dict[i[0]].append(con)
                gw_dict[i[0]].append({'1': state1, '2': state2, '3': state3, '4': state4, '5': state5})
            else:
                gantt_data = my_select1(sql1.format(timeto, cat_sql))
                if gantt_data:
                    gw_dict[gantt_data[0][0]] = []
                    for i in gantt_data:
                        state1 += i[2]
                        state4 += i[3]
                        con = {"state": i[1]}
                        gw_dict[i[0]].append(con)
                    gw_dict[i[0]].append({'1': state1, '2': state2, '3': state3, '4': state4, '5': state5})
            content['payload'] = gw_dict
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def global_kpi(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            jdl_sql = "SELECT FORMAT(sum(rt)/sum(ot)*100,2)  \
                      FROM `tb_meter_activation_by_day` WHERE rc>='{}'"
            watt_sql = "SELECT FORMAT(sum(energy),2) FROM `tb_watt_by_day` WHERE rc='{}'"
            timeto = datetime.datetime.now()
            in_time = timeto.strftime("%Y-%m-%d 00:00:00")
            yesterday = timeto.strftime("%Y-%m-%d")
            jdl_data = my_select1(jdl_sql.format(yesterday))
            watt_data = my_select1(watt_sql.format(yesterday))
            nom = 0
            keys = get_keys('st:meter:*')
            for i in keys:
                re_data = red_conn(i)
                if re_data:
                    ps_date = eval(re_data)
                    if int(ps_date['a_current']) >= 0.5:
                        nom += 1
            content['payload'] = {"activation": jdl_data[0][0], "watt": watt_data[0][0], "open": nom}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def site_jdl(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            jdl_sql = "SELECT FORMAT(sum(rt)*100/sum(ot),2) \
                      FROM `tb_meter_activation_by_day` WHERE rc>='{}' AND rc<='{}'"
            timeto = datetime.date.today()
            wk_time = timeto - relativedelta(days=6)
            month_time = timeto - relativedelta(months=1)
            time_wd = [timeto, wk_time, month_time]
            jdl_data = []
            for i in time_wd:
                data = my_select1(jdl_sql.format(i, timeto))
                if data:
                    jdl_data.append(data[0][0])
                else:
                    jdl_data.append(0)
            content['payload'] = jdl_data
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def site_watt(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            watt_sql = "SELECT sum(energy) FROM `tb_watt_by_day` where rc>='{}' AND rc<='{}'"
            timeto = datetime.date.today()
            wk_time = timeto - relativedelta(days=6)
            month_time = timeto - relativedelta(months=1)
            year_time = timeto - relativedelta(months=11)
            time_wd = [timeto, wk_time, month_time, year_time]
            watt_data = []
            for i in time_wd:
                data = my_select1(watt_sql.format(i, timeto))
                if data[0][0]:
                    watt_data.append(round(data[0][0], 2))
                else:
                    watt_data.append(0)
            content['payload'] = watt_data
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def pressure_total(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            tot_sql = "SELECT sections,count(gw_id) FROM (SELECT equipment_id \
                        FROM `sanse_deng_list` WHERE sensors_type = 'air_sensor' ) a \
                        LEFT JOIN st126_device b ON a.equipment_id = b.gw_id GROUP BY sections"
            machine_data = my_select1(tot_sql)
            content['payload'] = dict(machine_data)
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def pre_state(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            se_sql = "SELECT DISTINCT sections FROM sanse_deng_list a \
                    LEFT JOIN st126_device b on a.equipment_id=b.gw_id WHERE sensors_type='air_sensor' ORDER BY right(sections,4)"
            gw_sql = "SELECT equipment_id FROM sanse_deng_list a LEFT JOIN \
                st126_device b ON a.equipment_id=b.gw_id where sensors_type='air_sensor' and b.sections='{}'"
            se_data = my_select1(se_sql)
            se_total = OrderedDict()
            if se_data:
                for i in se_data:
                    nom = 0
                    gw_data = my_select1(gw_sql.format(i[0]))
                    total_gw = len(gw_data)
                    for j in gw_data:
                        re_data = red_conn("st:ps:" + j[0])
                        if re_data:
                            ps_date = list(eval(re_data).values())
                            if ps_date[0] >= '550' and ps_date[0] != 'Error':
                                nom += 1
                    se_total[i[0]] = [total_gw, nom]
            content['payload'] = se_total
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def pre_defect_cot(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            by_time = request.GET.get('by_time', '')
            timeto = datetime.date.today()
            if not by_time: raise Exception(u"請傳入需要查看的範圍")
            pre_total_sql = "SELECT gw_name,count(gw_name) err_total FROM `tb_iot_defect` a  \
                            LEFT JOIN st126_device b ON a.gw_id = b.gw_id WHERE a.defect_type = 'pressure' \
                            AND a.total_time > 300 AND DATE(start_time)>='{}' AND DATE(start_time)<='{}'\
                            GROUP BY gw_name ORDER BY  err_total DESC  limit 3"
            pre_time_sql = "SELECT gw_name,total_time err_total FROM `tb_iot_defect` a \
                         LEFT JOIN st126_device b ON a.gw_id = b.gw_id WHERE a.defect_type = 'pressure' \
                         AND a.total_time > 300 AND DATE(start_time)>='{}' AND DATE(start_time)<='{}'\
                       GROUP BY gw_name ORDER BY  err_total DESC  limit 3"
            pre_err_sql = "SELECT start_time,end_time,FORMAT(total_time/60,2),sections,gw_name,flag \
                         FROM `tb_iot_defect` a  LEFT JOIN st126_device b ON a.gw_id = b.gw_id \
                         WHERE a.defect_type = 'pressure'  AND a.total_time > 300  \
                         AND DATE(start_time) >= '{}' AND DATE(start_time) <= '{}'  ORDER BY start_time DESC"
            if by_time == 'day':
                str_time = timeto
            elif by_time == 'seven_day':
                str_time = timeto - relativedelta(days=6)
            elif by_time == 'month':
                str_time = timeto - relativedelta(months=1)
            elif by_time == 'three_month':
                str_time = timeto - relativedelta(months=2)
            elif by_time == 'six_month':
                str_time = timeto - relativedelta(months=5)
            else:
                str_time = timeto
            pre_total_data = my_select1(pre_total_sql.format(str_time, timeto))
            pre_time_data = my_select1(pre_time_sql.format(str_time, timeto))
            pre_err_data = my_select1(pre_err_sql.format(str_time, timeto))
            data1 = []
            if pre_err_data:
                for row in pre_err_data:
                    data_row = list(row)
                    data_row[0] = data_row[0].strftime("%Y-%m-%d %H:%M:%S")
                    data_row[1] = data_row[1].strftime("%Y-%m-%d %H:%M:%S")
                    data1.append(data_row)
            content['payload'] = {"pre_time": pre_time_data, "pre_total": pre_total_data, "pre_err_detail": data1}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def pre_detail(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            # if not sections: raise Exception(u"請傳入車間名稱")
            pre_detail_sql = "SELECT gw_id,sections,sanse_deng,gw_name,ip_address FROM `sanse_deng_list` a LEFT JOIN  \
                            st126_device b ON a.equipment_id=b.gw_id where sensors_type='air_sensor' and {} ORDER BY right(sections,4)"
            if not floor and not sections:
                cat_sql = '1=1'
            elif floor and not sections:
                cat_sql = "floor='%s'" % (floor)
            elif not floor and sections:
                cat_sql = "sections='%s'" % (sections)
            gw_data = my_select1(pre_detail_sql.format(cat_sql))
            pre_detail = []
            if gw_data:
                for i in gw_data:
                    ma_detail = {}
                    ma_detail['sections'] = i[1]
                    ma_detail['iot'] = i[2]
                    ma_detail['gw_name'] = i[3]
                    ma_detail['ip'] = i[4]
                    ma_detail['pre'] = 'NA'
                    ma_detail['flag'] = 0
                    re_data = red_conn("st:ps:" + i[0])
                    if re_data:
                        ps_date = list(eval(re_data).values())
                        if ps_date[0] != 'Error':
                            ma_detail['pre'] = round(int(ps_date[0]) / 1000, 2)
                        if ps_date[0] != 'Error' and int(ps_date[0]) >= 550:
                            ma_detail['flag'] = 1
                    pre_detail.append(ma_detail)
            content['payload'] = pre_detail
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def energy_trend(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            by_time = request.GET.get('by_time', '')
            timeto = datetime.date.today()
            if not by_time: raise Exception("請輸入查看類型")
            energy_sql = "SELECT {},sum(energy) FROM `tb_watt_by_hours1` WHERE \
	                  date(rc) >= '{}' AND DATE(rc) <= '{}' GROUP BY {}"
            flag = "day"
            if by_time == 'day':
                flag = "hours"
                by_type = "DATE_FORMAT(rc, '%H:%i')"
                str_time = timeto
            elif by_time == 'seven_day':
                by_type = "DATE_FORMAT(rc, '%Y-%m-%d')"
                str_time = timeto - relativedelta(days=6)
            elif by_time == 'month':
                by_type = "DATE_FORMAT(rc, '%Y-%m-%d')"
                str_time = timeto - relativedelta(months=1)
            elif by_time == 'year':
                flag = "month"
                by_type = "DATE_FORMAT(rc, '%Y-%m')"
                str_time = timeto - relativedelta(months=11)
            else:
                str_time = timeto
            energy_data = my_select1(energy_sql.format(by_type, str_time, timeto, by_type))
            content['payload'] = {'energy_trend': energy_data, 'flag': flag}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def nomral_top(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            by_time = request.GET.get('by_time', '')
            timeto = datetime.date.today()
            if not by_time: raise Exception("請輸入查看類型")
            energy_sql = "SELECT b.gw_name,sum(energy) total_enrgy FROM `tb_watt_by_day` a \
                          LEFT JOIN st126_device b ON a.gw_id = b.gw_id WHERE rc>='{}' and rc<='{}' \
                          GROUP BY a.gw_id ORDER BY total_enrgy DESC LIMIT 5"
            if by_time == 'day':
                str_time = timeto
            elif by_time == 'seven_day':
                str_time = timeto - relativedelta(days=6)
            elif by_time == 'month':
                str_time = timeto - relativedelta(months=1)
            elif by_time == 'year':
                str_time = timeto - relativedelta(months=11)
            else:
                str_time = timeto
            nomral_data = my_select1(energy_sql.format(str_time, timeto))
            content['payload'] = {'nomral_top': nomral_data}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def se_energy(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:

            by_time = request.GET.get('by_time', '')
            timeto = datetime.date.today()
            if not by_time: raise Exception("請輸入查看類型")
            # se_energy_sql = "SELECT sections,count(DISTINCT a.gw_id),sum(energy) FROM `tb_watt_by_day` a \
            #                 LEFT JOIN st126_device b ON a.gw_id = b.gw_id where rc>='{}' AND rc<='{}' \
            #                 AND sections is not null GROUP BY sections ORDER BY right(sections,4)"
            # san_sql = "SELECT sections,count(DISTINCT a.gw_id),sum(rt),sum(ot) FROM \
            #            `tb_activation_by_day1` a LEFT JOIN st126_device b ON a.gw_id=b.gw_id \
            #             WHERE rc>='{}'  AND  rc<='{}' AND sections='{}'"

            se_energy_sql = "SELECT e.sections,1,IFNULL(sum(e.t_energy),0),sum(e.nom_energy) \
                from (SELECT c.sections,c.gw_name,a.total_energy-if((a.total_energy * b.jd),(a.total_energy * b.jd),0) nom_energy,\
                      (a.total_energy) t_energy FROM (SELECT gw_id,sum(energy) total_energy FROM `tb_watt_by_day` \
                      WHERE rc >= '{0}' AND rc <= '{1}' GROUP BY gw_id) a left JOIN  \
                      (SELECT gw_id,(sum(ot) - sum(rt)) / sum(ot) jd FROM tb_activation_by_day1 WHERE \
                      rc >= '{0}' AND rc <= '{1}' GROUP BY gw_id ) b ON a.gw_id = b.gw_id  LEFT JOIN st126_device c \
                      ON a.gw_id = c.gw_id GROUP BY c.gw_name) e GROUP BY e.sections ORDER BY right(sections,4)"
            # se_energy_sql = ""
            if by_time == 'day':
                str_time = timeto
            elif by_time == 'seven_day':
                str_time = timeto - relativedelta(days=6)
            elif by_time == 'month':
                str_time = timeto - relativedelta(months=1)
            elif by_time == 'year':
                str_time = timeto - relativedelta(months=11)
            else:
                str_time = timeto
            se_total_energy = my_select1(se_energy_sql.format(str_time, timeto))
            se_data = []
            for i in se_total_energy:
                se_data.append(i)
            # if se_energy_sql:
            #     for i in se_total_energy:
            #         data_row = list(i)
            #         san_data = my_select1(san_sql.format(str_time, timeto, data_row[0]))
            #         if san_data[0][0]:
            #             for j in san_data:
            #                 avg_time = j[3] / j[1]
            #                 no_san = data_row[1] - j[1]
            #                 no_san_time = avg_time * no_san
            #                 jd = (j[2] + no_san_time) / (j[3] + no_san_time)
            #             data_row.append(data_row[2] * float(jd))
            #         else:
            #             data_row.append(data_row[2])
            #         se_data.append(data_row)
            content['payload'] = se_data
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def total_energy(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            timeto = datetime.date.today()
            # energy_sql = "SELECT count(DISTINCT a.gw_id),sum(energy) FROM `tb_watt_by_day` a \
            #             LEFT JOIN st126_device b ON a.gw_id=b.gw_id where rc>='{}' AND rc<='{}' AND {} "
            # san_sql = "SELECT count(DISTINCT a.gw_id),sum(rt),sum(ot) FROM `tb_activation_by_day1` a LEFT JOIN \
            #             st126_device b ON a.gw_id=b.gw_id WHERE rc>='{}' AND rc<='{}' AND {}"
            energy_sql = "SELECT sum(e.nom_energy),IFNULL(sum(e.t_energy), 0)-sum(e.nom_energy),IFNULL(sum(e.t_energy), 0)\
                FROM (SELECT c.sections,c.gw_name,c.floor,a.total_energy -IF ((a.total_energy * b.jd),(a.total_energy * b.jd),0) nom_energy,\
                (a.total_energy) t_energy FROM (SELECT gw_id,sum(energy) total_energy FROM `tb_watt_by_day`\
                WHERE rc >= '{0}' AND rc <= '{1}' GROUP BY gw_id) a LEFT JOIN ( SELECT gw_id, (sum(ot) - sum(rt)) / sum(ot) jd \
                FROM tb_activation_by_day1 WHERE rc >= '{0}' AND rc <= '{1}' GROUP BY gw_id ) b ON a.gw_id = b.gw_id \
                LEFT JOIN st126_device c ON a.gw_id = c.gw_id GROUP BY c.gw_name ) e WHERE {2}"
            if not start_time and not end_time:
                start_time = timeto - relativedelta(days=6)
                end_time = timeto
            if not floor and not sections:
                cat_sql = '1=1'
            elif floor and not sections:
                cat_sql = "e.floor='%s'" % (floor)
            elif sections:
                cat_sql = "e.sections='%s'" % (sections)
            energy_data = my_select1(energy_sql.format(start_time, end_time, cat_sql))
            t_energy = {}
            if energy_data:
                for i in energy_data:
                    t_energy['total_energy'] = i[2]
                    t_energy['energy_state'] = [i[0], i[1]]
            # if energy_data[0][0] and san_data[0][0]:
            #     avg_time = san_data[0][2] / san_data[0][0]
            #     no_san = energy_data[0][0] - san_data[0][0]
            #     no_san_time = avg_time * no_san
            #     jd = float((san_data[0][1] + no_san_time) / (san_data[0][2] + no_san_time))
            #     content['payload'] = {'total_energy': energy_data[0][1],
            #                           'energy_state': [energy_data[0][1] * jd, energy_data[0][1] * (1 - jd)]}
            # elif not san_data[0][0] and energy_data[0][0]:
            #     content['payload'] = {'total_energy': energy_data[0][1],
            #                           'energy_state': [energy_data[0][1], 0]}
            # elif not san_data[0][0] and not energy_data[0][0]:
            #     content['payload'] = {'total_energy': 0,
            #                           'energy_state': [0, 0]}
            content['payload'] = t_energy
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def pro_energy(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            timeto = datetime.date.today()
            pro_sql = "SELECT provider,sum(energy) total_enrgy FROM `tb_watt_by_day` a  \
                        LEFT JOIN st126_device b ON a.gw_id=b.gw_id \
                        WHERE rc>='{}' AND rc<='{}' AND {} AND provider is not null \
                        GROUP BY provider ORDER BY total_enrgy desc"
            if not start_time and not end_time:
                start_time = timeto - relativedelta(days=6)
                end_time = timeto
            if not floor and not sections:
                cat_sql = '1=1'
            elif floor and not sections:
                cat_sql = "floor='%s'" % (floor)
            elif sections:
                cat_sql = "sections='%s'" % (sections)
            pro_data = my_select1(pro_sql.format(start_time, end_time, cat_sql))
            content['payload'] = pro_data
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def se_energy_trend(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            timeto = datetime.date.today()
            flag = 'day'
            if not start_time and not end_time:
                start_time = timeto - relativedelta(days=6)
                end_time = timeto
            if start_time == end_time:
                flag = 'hours'
                gw_trend = "SELECT DATE_FORMAT(rc ,'%H:%i'),sum(energy) FROM `tb_watt_by_hours1` a LEFT JOIN \
                      st126_device b ON a.gw_id = b.gw_id WHERE date(rc) >= '{}' AND DATE(rc)<='{}' and {}\
                        GROUP BY DATE_FORMAT(rc, '%H:%i')"
            else:
                gw_trend = "SELECT rc,sum(energy) FROM tb_watt_by_day a LEFT JOIN st126_device b ON a.gw_id=b.gw_id \
                        WHERE rc>='{}' AND rc<='{}' and {} GROUP BY rc"

            if not floor and not sections:
                cat_sql = '1=1'
            elif floor and not sections:
                cat_sql = "floor='%s'" % (floor)
            elif sections:
                cat_sql = "sections='%s'" % (sections)
            pro_data = my_select1(gw_trend.format(start_time, end_time, cat_sql))
            content['payload'] = {'energy_trend': pro_data, 'flag': flag}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def energy_ex_top(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            timeto = datetime.date.today()
            pro_sql = "SELECT c.gw_name,(a.total_energy*b.jd) ex_energy FROM (SELECT gw_id,sum(energy) total_energy \
                       FROM `tb_watt_by_day` WHERE rc>='{0}' AND rc<='{1}' GROUP BY gw_id) a RIGHT  JOIN \
                       (SELECT gw_id,(sum(ot)-sum(rt))/sum(ot) jd FROM tb_activation_by_day1 \
                        WHERE rc>='{0}' AND rc<='{1}' GROUP BY gw_id ) b ON a.gw_id=b.gw_id \
                        LEFT JOIN st126_device c ON b.gw_id=c.gw_id WHERE {2} ORDER BY ex_energy DESC limit 5"
            if not start_time and not end_time:
                start_time = timeto - relativedelta(days=6)
                end_time = timeto
            if not floor and not sections:
                cat_sql = '1=1'
            elif floor and not sections:
                cat_sql = "floor='%s'" % (floor)
            elif sections:
                cat_sql = "sections='%s'" % (sections)
            pro_data = my_select1(pro_sql.format(start_time, end_time, cat_sql))
            content['payload'] = pro_data
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def pro_trend(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            provider = request.GET.get('provider', '')
            if not provider: raise Exception("請選擇需要查看的品牌")
            timeto = datetime.date.today()
            by_type = "DATE_FORMAT(rc, '%Y-%m-%d')"
            flag = "day"
            pro_sql = "SELECT rc,sum(energy) FROM `tb_watt_by_day` a LEFT JOIN st126_device b ON a.gw_id=b.gw_id \
                        WHERE b.provider='{}' AND rc>='{}' AND rc<='{}' GROUP BY rc"
            san_sql = "SELECT a.gw_id,IFNULL(sum(rt) / SUM(ot),0) , IFNULL(SUM(ex) / SUM(ot),0), \
                        IFNULL(SUM(nm) / SUM(ot), 0) FROM  tb_activation_by_day1 a LEFT JOIN \
                      st126_device b ON a.gw_id=b.gw_id WHERE rc>='{}' AND rc<='{}' AND provider='{}' GROUP BY a.gw_id"
            energy_sql = "SELECT a.gw_id,b.gw_name,sum(energy) FROM `tb_watt_by_day` a LEFT JOIN st126_device b \
                         ON a.gw_id=b.gw_id  WHERE rc>='{}' AND rc<='{}' AND provider='{}' GROUP BY a.gw_id"
            if not start_time and not end_time:
                start_time = timeto - relativedelta(days=6)
                end_time = timeto
            if start_time == end_time:
                pro_sql = "SELECT DATE_FORMAT(rc, '%H:%i'),sum(energy) FROM `tb_watt_by_hours1`  a LEFT JOIN  st126_device b ON a.gw_id=b.gw_id \
                            WHERE b.provider='{}' AND date(rc) >= '{}' AND DATE(rc) <= '{}' GROUP BY DATE_FORMAT(rc, '%H:%i')"
                flag = "hours"
            san_data = my_select1(san_sql.format(start_time, end_time, provider))
            energy_data = my_select1(energy_sql.format(start_time, end_time, provider))
            pro_data = my_select1(pro_sql.format(provider, start_time, end_time))
            san_id_list = [[i[0], i[1], i[2], i[3]] for i in san_data]
            gw_list = []
            if energy_data:
                for i in energy_data:
                    gw_conn = {}
                    gw_conn['gw_id'] = i[1]
                    gw_conn['total_energy'] = i[2]
                    gw_conn['normal'] = 0
                    gw_conn['unusual'] = 0
                    gw_conn['working'] = 0
                    for j in san_id_list:
                        if i[0] in j:
                            gw_conn['normal'] = round(i[2] * float(j[1]), 2)
                            gw_conn['unusual'] = round(i[2] * float(j[2]), 2)
                            gw_conn['working'] = round(i[2] * float(j[3]), 2)
                    gw_list.append(gw_conn)

            content['payload'] = {"pro_trend": pro_data, "gw_data": gw_list, "flag": flag}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def energy_id(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            gw_name = request.GET.get('gw_name', '')
            if not gw_name: raise Exception("請選擇需要查看的設備")
            if not start_time: raise Exception("請選擇需要查看的時間段")
            timeto = datetime.date.today()
            flag = 'day'
            gw_time = "SELECT gw_name,sum(nm),(sum(rt) - sum(nm)) AS dj,(sum(ex) - sum(ht)) AS ex,sum(ht) \
                       FROM `tb_activation_by_day1` a LEFT JOIN st126_device b  \
                       ON a.gw_id=b.gw_id WHERE gw_name='{}' AND rc>='{}' AND rc<='{}'"
            if start_time == end_time:
                flag = 'hours'
                gw_trend = "SELECT DATE_FORMAT(rc ,'%H:%i'),sum(energy) FROM `tb_watt_by_hours1` a LEFT JOIN \
                      st126_device b ON a.gw_id = b.gw_id WHERE gw_name = '{}' AND date(rc) >= '{}' AND DATE(rc)<='{}'\
                        GROUP BY DATE_FORMAT(rc, '%H:%i')"
            else:
                gw_trend = "SELECT rc,sum(energy) FROM tb_watt_by_day a LEFT JOIN st126_device b ON a.gw_id=b.gw_id \
                        WHERE gw_name = '{}' AND rc>='{}' AND rc<='{}' GROUP BY rc"
            gw_time_data = my_select1(gw_time.format(gw_name, start_time, end_time))
            gw_trend_data = my_select1(gw_trend.format(gw_name, start_time, end_time))
            content['payload'] = {'energy_time': gw_time_data, 'energy_trend': gw_trend_data, 'flag': flag}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def watt_forms(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            floor = request.GET.get('floor', '')
            sections = request.GET.get('sections', '')
            provider = request.GET.get('provider', '')
            level = request.GET.get('level', '')
            gw_name = request.GET.get('gw_name', '')
            start_time = request.GET.get('start_time', '')
            end_time = request.GET.get('end_time', '')
            timeto = datetime.date.today()
            watt_forms_sql = "SELECT concat(site,'_',building,'_',floor,'_',sections),provider,level_1,\
                    a.gw_id,gw_name,SUM(energy) FROM `tb_watt_by_day` a LEFT JOIN st126_device b ON a.gw_id = b.gw_id \
                    WHERE rc >= '{}' AND rc <= '{}' AND {} AND enable_flag=1 AND base_type = 'equipment' \
                    AND gw_name is not null GROUP BY a.gw_id"
            # san_sql = "SELECT a.gw_id,IFNULL((SUM(nomal) + SUM(standby)) / SUM(300),0) , IFNULL((SUM(exception) + sum(STOP)) / SUM(300),0), \
            #             IFNULL(SUM(nomal) / SUM(300), 0) FROM tb_machine_gantt a LEFT JOIN st126_device b ON a.gw_id=b.gw_id WHERE {} {} GROUP by gw_id"
            san_sql = "SELECT a.gw_id,IFNULL(sum(rt) / sum(ot), 0),IFNULL(sum(ex) / sum(ot), 0), \
                      IFNULL(sum(nm) / sum(ot), 0) FROM `tb_activation_by_day1` a LEFT JOIN st126_device b ON  \
                     a.gw_id = b.gw_id WHERE rc >= '{}' AND rc <= '{}' and {} GROUP BY gw_id"
            if not start_time and not end_time:
                start_time = timeto - relativedelta(days=6)
                end_time = timeto
            if not floor:
                cat_sql = '1=1'
            elif floor and not sections:
                cat_sql = "floor='%s'" % (floor)
            elif sections and not provider:
                cat_sql = "floor='%s' and sections='%s'" % (floor, sections)
            elif provider and not level:
                cat_sql = "floor='%s' and sections='%s' and provider='%s'" % (floor, sections, provider)
            elif level and not gw_name:
                cat_sql = "floor='%s' and sections='%s' and provider='%s' and level_1='%s' " \
                          % (floor, sections, provider, level)
            elif gw_name:
                cat_sql = "floor='%s' and sections='%s' and provider='%s' and level_1='%s' and gw_name='%s'" \
                          % (floor, sections, provider, level, gw_name)
            san_energy = my_select1(watt_forms_sql.format(start_time, end_time, cat_sql))
            san_state_id = my_select1(san_sql.format(start_time, end_time, cat_sql))
            san_state_id_list = [[i[0], i[1], i[2], i[3]] for i in san_state_id]
            gw_list = []

            if not san_energy:
                content['payload'] = {}
            else:
                for i in san_energy:
                    gw_conn = {}
                    gw_conn['gw_name'] = i[4]
                    gw_conn['total_energy'] = i[5]
                    gw_conn['loca'] = i[0]
                    gw_conn['provider'] = i[1]
                    gw_conn['level'] = i[2]
                    gw_conn['normal'] = 0
                    gw_conn['unusual'] = 0
                    gw_conn['working'] = 0
                    for j in san_state_id_list:
                        if i[3] in j:
                            gw_conn['normal'] = i[5] * float(j[1])
                            gw_conn['unusual'] = i[5] * float(j[2])
                            gw_conn['working'] = i[5] * float(j[3])
                    gw_list.append(gw_conn)
                content['payload'] = gw_list
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST"])
def resume(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            timeto = datetime.date.today()
            gw_id = request.GET.get('gw_id', '')
            if not gw_id: raise Exception("請傳入id")
            resume_sql = "SELECT c.*,d.repair_date FROM (SELECT a.*,b.img_url,b.gw_name FROM `tb_st_device_test` a LEFT JOIN \
                          st126_device b ON a.gw_id=b.gw_id WHERE a.gw_id='{}') c  \
                          LEFT JOIN tb_repair1 d ON c.st_num = d.st_num ORDER BY repair_date DESC limit 1"
            data = my_select1(resume_sql.format(gw_id))
            resume_data = []
            use_date = 0
            if data:
                for i in data:
                    if i[14] and i[14] !="無":
                        on_time = datetime.datetime.strptime(i[14], '%Y-%m-%d')
                        month_differ = (timeto.year - on_time.year) * 12 + (timeto.month - on_time.month) * 1
                        use_date = round(month_differ / 12, 2)
                    resume = {}
                    resume['id'] = i[0]
                    resume['group'] = i[1]
                    resume['site'] = i[2]
                    resume['bu'] = i[3]
                    resume['st_num'] = i[4]
                    resume['cost_no'] = i[5]
                    resume['device'] = i[6]
                    resume['org_no'] = i[7]
                    resume['org'] = i[8]
                    resume['brand'] = i[9]
                    resume['made_in'] = i[10]
                    resume['level'] = i[11]
                    resume['entry_date'] = i[12]
                    resume['accept_date'] = i[13]
                    resume['pd_date'] = i[14]
                    resume['use_date'] = use_date
                    resume['position'] = i[50]
                    resume['pd_no'] = i[17]
                    resume['asset_no'] = i[18]
                    resume['customs_no'] = i[19]
                    resume['property_no'] = i[20]
                    resume['manager'] = i[21]
                    resume['power'] = i[22]
                    resume['voltage'] = i[23]
                    resume['size'] = i[24]
                    resume['weight'] = i[25]
                    resume['air_pressure'] = i[26]
                    resume['air'] = i[27]
                    resume['system'] = i[28]
                    resume['speed'] = i[29]
                    resume['tool_type'] = i[30]
                    resume['ip'] = i[31]
                    resume['device_manager'] = i[32]
                    resume['maintain_name'] = i[33]
                    resume['maintain_no'] = i[34]
                    resume['maintain_manager'] = i[35]
                    resume['maintain_team'] = i[36]
                    resume['usage'] = i[37]
                    resume['desc1'] = i[38]
                    resume['gw_id'] = i[39]
                    resume['nc_battery_brand'] = i[40]
                    resume['nc_battery_level'] = i[41]
                    resume['drive_battery_brand'] = i[42]
                    resume['drive_battery_level'] = i[43]
                    resume['assist_name'] = i[44]
                    resume['assist_brand'] = i[45]
                    resume['assist_level'] = i[46]
                    resume['assist_pd_no'] = i[47]
                    resume['img_url'] = i[49]
                    resume['repair_date'] = i[51]
                    resume_data.append(resume)
            else:
                raise Exception("沒有找到對應的數據!")
            content['payload'] = resume_data
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)

    elif request.method == 'POST':
        content = {'errmsg': ''}
        try:
            data = request.data
            up_sql = "UPDATE tb_st_device_test SET speed='{}',maintain_no='{}',tool_type='{}',`size`='{}',power='{}',ip='{}',\
                    maintain_name='{}',maintain_manager='{}',air='{}',weight='{}',voltage='{}',maintain_team='{}',\
                    system='{}' where id= {} "
            insert_data(
                up_sql.format(data['speed'], data['maintain_no'], data['tool_type'], data['size'], data['power'],
                              data['ip'], data['maintain_name'], data['maintain_manager'], data['air'], data['weight'],
                              data['voltage'], data['maintain_team'], data['system'], data['id']))
            content['payload'] = 'OK'
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST"])
def repair_msg(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            st_num = request.GET.get('st_num', '')
            if not st_num: raise Exception("請傳入st_num")
            resume_sql = "SELECT repair_date,repair_hours,repair_process,operator FROM `tb_repair1` WHERE st_num='{}'\
                         and repair_date!='' ORDER BY repair_date DESC"
            data = my_select1(resume_sql.format(st_num))
            repair_year = []
            t_data = []
            for i in data:
                if i[0][0:4] not in repair_year:
                    repair_year.append(i[0][0:4])
            for year in repair_year:
                j = 0
                repair_data = []
                repair_hours = 0
                for value in data:
                    repair_datail = {}
                    if year in value[0]:
                        j += 1
                        repair_hours += value[1]
                        repair_datail['repair_date'] = value[0]
                        repair_datail['repair_hours'] = value[1]
                        repair_datail['repair_process'] = value[2]
                        repair_datail['operator'] = value[3]
                        repair_data.append(repair_datail)
                repair_data.append([year, j, repair_hours])
                t_data.append(repair_data)
            content['payload'] = t_data
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def resume_watt(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            gw_id = request.GET.get('gw_id', '')
            by_time = request.GET.get('by_time', 'day')
            timeto = datetime.date.today()
            if not by_time: raise Exception("請輸入查看類型")
            if not gw_id: raise Exception("請傳入id")
            if by_time == 'day':
                str_time = timeto
            elif by_time == 'seven_day':
                str_time = timeto - relativedelta(days=6)
            elif by_time == 'month':
                str_time = timeto - relativedelta(months=1)
            watt_sql = "SELECT sum(rt),sum((ot-rt)) FROM `tb_meter_activation_by_day` where  gw_id='{}' AND rc>='{}' AND rc<='{}'"
            san_sql = "SELECT sum(nm),(sum(rt) - sum(nm)) AS dj,(sum(ex) - sum(ht)) AS ex,sum(ht) \
                        from tb_activation_by_day1 WHERE gw_id='{}' AND rc>='{}' AND rc<='{}'"
            state = {}
            data = my_select1(san_sql.format(gw_id, str_time, timeto))
            if data[0][0]:
                for i in data:
                    state['nomal'] = i[0]
                    state['standby'] = i[1]
                    state['exception'] = i[2]
                    state['stop'] = i[3]
            else:
                data = my_select1(watt_sql.format(gw_id, str_time, timeto))
                if data:
                    for i in data:
                        state['nomal'] = i[0]
                        state['standby'] = 0
                        state['exception'] = 0
                        state['stop'] = i[1]
            content['payload'] = state
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def device_nav(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            site = request.GET.get('site', '')
            # 0<3年,1：=3--5年,2： ==5--8年,3：==8--12年,4：>=12年
            use_date = request.GET.get('use_date', '0')
            org = request.GET.get('org', '')
            brand = request.GET.get('brand', '')
            sql = "SELECT DISTINCT {0} FROM `tb_st_device_test` where {0}!='' {1}"
            if site == "":
                sql = sql.format('site', '')
            if site and org == "":
                f_sql = "and site = '{}'".format(site)
                sql = sql.format('org', f_sql)
            if org and brand == "":
                f_sql = "and site ='{}' and org = '{}'".format(site, org)
                sql = sql.format('brand', f_sql)
            data = my_select1(sql)
            content['payload'] = [i[0] for i in data]
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def use_top(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            site = request.GET.get('site', 'SZLH')
            sql = "SELECT st_num,pd_date FROM `tb_st_device_test` WHERE site='{}' \
                   AND pd_date!='' ORDER BY pd_date asc limit 5"
            data = my_select1(sql.format(site))
            if not data: raise Exception("無數據")
            datas = []
            for i in data:
                datas.append([i[0], get_use_date(i[1])])
            content['payload'] = datas
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def use_brand(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            site = request.GET.get('site', 'SZLH')
            sql = "SELECT brand,pd_date FROM `tb_st_device_test` WHERE site='{}' \
                   AND pd_date!='' AND pd_date!='無' and brand!=''"
            brand_sql = "SELECT brand,count(*) FROM tb_st_device_test WHERE site='{}' AND pd_date!=''  \
                       AND pd_date!='無' and brand!='' GROUP BY brand ORDER BY count(*) desc"
            data = my_select1(sql.format(site))
            brand_data = my_select1(brand_sql.format(site))
            if not data: raise Exception("無數據")
            datas = []
            for i in brand_data:
                brand = {}
                total = 0
                brand['brand'] = i[0]
                it_names = ['flag1', 'flag2', 'flag3', 'flag4', 'flag5']
                it = TimeInterval()
                for j in data:
                    if j[0] in i[0]:
                        total += 1
                        time_inter = it.get_interval(j[1])
                for it_name in it_names:
                    brand[it_name] = time_inter[it_names.index(it_name)]
                brand['total'] = total
                datas.append(brand)
            content['payload'] = datas
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def use_total(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            site = request.GET.get('site', '')
            org = request.GET.get('org', '')
            sql = "SELECT pd_date FROM `tb_st_device_test` WHERE pd_date!='' and pd_date !='無' {0}"
            f_sql1 = ""
            f_sql2 = ""
            if site and not org:
                f_sql1 = "and site='{}'".format(site)
                f_sql2 = "and site='{}'".format(site)
            if site and org:
                f_sql2 = "and site='{}'".format(site)
                f_sql1 = "and site='{}' and org='{}' ".format(site, org)
            total_data = my_select1(sql.format(f_sql2))
            team_data = my_select1(sql.format(f_sql1))
            device_org = []
            device_total = []
            it_names = ['flag1', 'flag2', 'flag3', 'flag4', 'flag5']
            it = TimeInterval()
            it1 = TimeInterval()
            if total_data:
                for i in total_data:
                    time_inter = it.get_interval(i[0])
                if team_data:
                    for i in team_data:
                        time_inter1 = it1.get_interval(i[0])
                else:
                    time_inter1 = it1.get_interval('')
            else:
                time_inter = it.get_interval('')
                time_inter1 = it1.get_interval('')
            for it_name in it_names:
                temp1 = {}
                temp2 = {}
                temp1['name'] = it_name
                temp1['data'] = time_inter[it_names.index(it_name)]
                temp2['name'] = it_name
                temp2['data'] = time_inter1[it_names.index(it_name)]
                device_total.append(temp1)
                device_org.append(temp2)
            content['payload'] = {"device_total": device_total, "device_org": device_org}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def use_detail(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            site = request.GET.get('site', '')
            org = request.GET.get('org', '')
            brand = request.GET.get('brand', '')
            num_page = request.GET.get('page', '1')
            use_date = request.GET.get('use_date', '')
            use_mapping = {'flag1': 0, 'flag2': 3, 'flag3': 5, 'flag4': 8, 'flag5': 12}
            use_mapping1 = {'flag1': 3, 'flag2': 5, 'flag3': 8, 'flag4': 12, 'flag5': 10000}
            f_sql = []
            sql = "SELECT a.st_num,a.pd_date,brand,org,a.position,a.`level`,b.img_url,a.device FROM \
                  `tb_st_device_test` a LEFT JOIN st126_device b ON a.gw_id=b.gw_id where 1=1 {}"
            if site:
                f_sql.append("and a.site = '{}'".format(site))
            if org:
                f_sql.append("and a.org = '{}'".format(org))
            if brand:
                f_sql.append("and a.brand = '{}'".format(brand))
            m_sql = "".join(f_sql)
            data = my_select1(sql.format(m_sql))
            device_sum = []
            if data:
                for i in data:
                    device_detail = {}
                    device_detail['st_num'] = i[0]
                    device_detail['use_date'] = get_use_date(i[1])
                    device_detail['brand'] = i[2]
                    device_detail['org'] = i[3]
                    device_detail['position'] = i[4]
                    device_detail['level'] = i[5]
                    device_detail['img_url'] = i[6]
                    device_detail['name'] = i[7]
                    device_sum.append(device_detail)
            device_sum1 = []
            if use_date:
                for i in device_sum:
                    if i['use_date'] >= use_mapping[use_date] and i['use_date'] < use_mapping1[use_date]:
                        device_sum1.append(i)
                device_sum = device_sum1
            if num_page:
                pag_class = Paginator(device_sum, 24)
            else:
                pag_class = Paginator(device_sum, len(device_sum) + 1)
                num_page = 1
            select_data = pag_class.page(num_page)
            content['payload'] = {'deviec_data': select_data.object_list, 'in_page': int(num_page),
                                  'total': pag_class.count, 'total_page': pag_class.page_range[-1]}
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)



@api_view(http_method_names=["GET"])
def device_detail(request):
    if request.method == 'GET':
        content = {'errmsg': ''}
        try:
            content['status'] = 1
        except Exception as e:
            content['errmsg'] = str(e)
            content['status'] = 0
        finally:
            return Response(content)


